cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DarrenBL Frequent Visitor
Frequent Visitor

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:

Table1.JPG

 

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:

Table2.JPG

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@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

3 REPLIES 3
Super User
Super User

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

@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

Super User
Super User

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

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

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

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!