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

[DarrenBL]

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?

[CheenuSing]

Hi @DarrenBL

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]

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)

[Zubair_Muhammad · Accepted solution]

@DarrenBL

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

[PradeepDive]

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

[Anonymous]

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