Create a Calendar Table based on a Table (From and To)

[ChristianDGreat]

So we have a table like this in our Database

Date Start	Date End	Product	Season
1/21/2022	1/22/2023	A	2023
6/2/2022	6/4/2023	B	2023
10/2/2022	10/3/2023	C	2023
1/22/2021	1/20/2022	A	2022
6/3/2021	6/1/2022	B	2022
10/5/2021	10/1/2022	C	2022

You can see on Row 1, that on Product A, its own calendar start at 1/21/22 and ends on 1/22/2023 and so forth.

I want to build something like this

Date	Product	Season
1/21/2022	A	2023
.	A	2023
.	A	2023
.	A	2023
.	A	2023
1/22/2023	A	2023
6/2/2022	B	2023
.	B	2023
.	B	2023
.	B	2023
.	B	2023
6/4/2023	B	2023

The . . . means the date  between the from and to. (if it makes sense). (so on row 2 the date will be 1/22, then row 3 will be 1/23 and so on and so forth)

Is this possible?

Thanks

Chris

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5JDsAgCAD/4tmErfVefQbx/98QW1CbYDKHkUE1EbANMqc8mSeL8WPvxZ41FeBwClyh1FMh3I6xhNR+kgfoi2F88Bh7TMIpsG6rp2KBe+3BLbUl9QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Start" = _t, #"Date End" = _t, Product = _t, Season = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Start", type date}, {"Date End", type date}, {"Product", type text}, {"Season", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Date Start], Duration.TotalDays([Date End]-[Date Start])+1, #duration(1,0,0,0))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Product", "Season", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Date"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type1"

Pat

[ChristianDGreat]

Thanks! I was able to do that too using List in power query, thanks!