Forum Discussion

ChristianDGreat's avatar
ChristianDGreat
Resolver I
3 years ago
Solved

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

So we have a table like this in our Database

 

Date StartDate EndProductSeason
1/21/20221/22/2023A2023
6/2/20226/4/2023B2023
10/2/202210/3/2023C2023
1/22/20211/20/2022

A

 

2022
6/3/20216/1/2022B2022
10/5/202110/1/2022C2022

 

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

DateProductSeason
1/21/2022A2023
.A2023
.A2023
.A2023
.A2023
1/22/2023A2023
6/2/2022B2023
.B2023
.B2023
.B2023
.B2023
6/4/2023B2023

 

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

Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    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

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    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