Results 1 to 1 of 1

Thread: PQ - Only year-week used in data file - Best practice

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    236
    Rep Power
    7

    Cool PQ - Only year-week used in data file - Best practice

    (question from the web)
    Our data is received on week level - So the 3 options available are YEAR-WEEK / YEAR / WEEK

    My aim is to also show the months and quarters.
    I did create a date table but there is no match since I cannot convert one of the 3 options into a date.
    When I try convert i.e. the year 2019 into a date it shows me 12th of july 1905.

    What is the best way to get this solved?
    Working from a date table --> Data or the other way around and adjust the data and add a column with a specific date to the year-week data?

    Sorry if this is a bit of a noob question - already learned that it's important so want to do it well.
    • You didn't say what kind of date you need
    • Best practice - with what???
    • Any example of source data and expected result (xlsx file is welcome)
    • your description is unclear and not sufficient



    Year Year DSOY DEOY
    2019
    2019
    01/01/2019
    31/12/2019

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        DFDSOY = Table.AddColumn(Source, "DSOY", each Date.From(Date.StartOfYear(#datetime([Year],1,1,0,0,0)))),
        DFDEOY = Table.AddColumn(DFDSOY, "DEOY", each Date.From(Date.EndOfYear(#datetime([Year],1,1,0,0,0)))),
        Type = Table.TransformColumnTypes(DFDEOY,{{"Year", Int64.Type}, {"DSOY", type date}, {"DEOY", type date}})
    in
        Type
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
    https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 12-14-2023 at 02:43 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. Replies: 7
    Last Post: 05-20-2014, 02:10 AM
  2. Data Validation For Selecting Date And Week Number
    By paul_pearson in forum Excel Help
    Replies: 8
    Last Post: 06-16-2013, 05:07 AM
  3. Replies: 17
    Last Post: 12-18-2012, 04:15 PM
  4. Arrange the data by year and format.
    By pesteness in forum Excel Help
    Replies: 15
    Last Post: 08-19-2012, 08:54 PM
  5. Week Number And Week Start Day of Week
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 10-24-2011, 07:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •