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

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)