Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm trying to create a query that lists all the calendar days between 2 dates for all locations. I'd like the date to start at the first of the year based off the oldest date from my list. and end at the year-end of the most recent date from the list. (i.e. earliest date is 3/8/2020 so I want my dates starting at 1/1/2020 and ending at 12/31/2021)
This is just a small sample of the data. There are many locations with many different dates.
Location | Date |
New York | 7/9/2020 |
New York | 5/11/2021 |
New York | 2/2/2021 |
New York | 3/8/2020 |
New York | 11/25/2020 |
Philadelphia | 1/5/2021 |
Philadelphia | 6/19/2020 |
Philadelphia | 9/9/2021 |
Philadelphia | 10/15/2020 |
I'm looking to get something like this
Location | Date |
New York | 1/1/2020 |
New York | 1/2/2020 |
New York | 1/3/2020 |
New York | 1/4/2020 |
… | |
New York | 12/29/2021 |
New York | 12/30/2021 |
New York | 12/31/2021 |
Philadelphia | 1/1/2020 |
Philadelphia | 1/2/2020 |
… | |
Philadelphia | 12/29/2021 |
Philadelphia | 12/30/2021 |
Philadelphia | 12/31/2021 |
I thinked I'd prefer to do this in power query but any DAX solutions would do as well. Any help would be greatly appreciated. Thanks in advance.
Solved! Go to Solution.
You can try this in blank query and adjust the steps accordingly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lEy17fUNzIwMlCK1UERN9U3NARJGKJLGOkbYRU31rfAahDIGFOETEBGZk5iSmpOQUZmIkhW3xRhGpqcmb6hJU6NlhB3Y9VoaKBvCLMyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Date = _t]),
Grouped = Table.Group(Source, {"Location"}, {{"Gr", each List.Transform({Number.From(Date.StartOfYear (Date.From(List.Min(_[Date]))))..Number.From( Date.EndOfYear(Date.From(List.Max(_[Date]))))}, Date.From)}}),
#"Expanded Gr" = Table.ExpandListColumn(Grouped, "Gr"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Gr",{{"Gr", type date}})
in
#"Changed Type"
You can try this in blank query and adjust the steps accordingly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lEy17fUNzIwMlCK1UERN9U3NARJGKJLGOkbYRU31rfAahDIGFOETEBGZk5iSmpOQUZmIkhW3xRhGpqcmb6hJU6NlhB3Y9VoaKBvCLMyFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, Date = _t]),
Grouped = Table.Group(Source, {"Location"}, {{"Gr", each List.Transform({Number.From(Date.StartOfYear (Date.From(List.Min(_[Date]))))..Number.From( Date.EndOfYear(Date.From(List.Max(_[Date]))))}, Date.From)}}),
#"Expanded Gr" = Table.ExpandListColumn(Grouped, "Gr"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Gr",{{"Gr", type date}})
in
#"Changed Type"
I think I need to use this as well, but I'm having trouble replacing everything where I need to. Can you specify what goes where by any chance?
works great. Thank you