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 inclusive for each ID, that would look something like this:

I am having trouble with the part where I want a new row for each date in between the start and end date. Does anyone have any suggestions?

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

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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
      6 years ago

      how would I do the same thing if I only wanted EOM dates instead?

    • PradeepDive's avatar
      PradeepDive
      Helper II
      5 years ago

      Thats awesome... curious to know how that works.. can you explain.

  • 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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Needs some work but this should provide the general idea and get you started.

     

    Table = 
VAR __table1 = FILTER(ALL('Table11'),[ID]=100)
VAR __table2 = FILTER(ALL('Table11'),[ID]=101)
VAR __table3 = FILTER(ALL('Table11'),[ID]=102)
VAR __tableA = GENERATESERIES(MINX(__table1,[Start]),MAXX(__table1,[End]),1)
VAR __tableB = GENERATESERIES(MINX(__table2,[Start]),MAXX(__table2,[End]),1)
VAR __tableC = GENERATESERIES(MINX(__table3,[Start]),MAXX(__table3,[End]),1)
VAR __tableA1 = CROSSJOIN(__tableA,__table1)
VAR __tableB1 = CROSSJOIN(__tableB,__table2)
VAR __tableC1 = CROSSJOIN(__tableC,__table3)
RETURN
UNION(__tableA1,__tableB1,__tableC1)