PQ - Only year-week used in data file - Best practice
(question from the web)
Quote:
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
