Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a new table with multiple rows for each ID based on date.

Hello everyone, and thank you in advance.    I have a dataset with this general form:   I am trying to generate a table that has a row for each date in between the start and end date inclus...
General Comment
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Anonymous

    With Query Editor

     

    Add this Custom Column

     

    ={Number.From([Start])..Number.From([End])}

    Now Expand it to new rows

     

    Now convert this column into date format

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous

 

USing Edit Query on the table

 

Paste the following code in the Advanced Editor

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjDUByIjA0NLKMcYxjE1AAKlWB2QSkOopBFM0tAUiWNkClYZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, StartDate = _t, EndDate = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}, {"Number", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListGen", each List.Transform({Number.From([StartDate])..Number.From([EndDate])}, each Date.From(_))),
    #"Expanded ListGen" = Table.ExpandListColumn(#"Added Custom", "ListGen"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ListGen",{{"ListGen", type date}})
in
    #"Changed Type1"

 

Change the Source with your table.

 

If this works for you mark this as solution and also give KUDOS.

 

Cheers

 

CheenuSing