Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mardin
Advocate IV
Advocate IV

Transform [StartDate] and [FinishDate] to table with all dates in between + add average value next

Hi,

 

I have a simply table with 3 columns [StartDate], [FinishDate] and [Daily Average Value]

 

I want to transform it to a table that shows all dates between [StartDate] and [FinishDate] as individual rows, while also automatically filling the [Daily Average Value] in next to each [Date] accordingly as show in my mock-up below.

 

I can do this manually but not with hundreds of lines in the input table. Is there a way to have PowerBI / Query do this automatically?

 

 ---

 

Input table:

StartDateFinishDateDaily Average
01-03-201605-03-201610
06-03-201615-03-201615
15-03-201620-03-201612

 

Final table:

DateDaily average
01-03-201610
02-03-201610
03-03-201610
04-03-201610
05-03-201610
06-03-201615
07-03-201615
08-03-201615
09-03-201615
10-03-201615
11-03-201615
12-03-201615
13-03-201615
14-03-201615
15-03-201615
16-03-201612
17-03-201612
18-03-201612
19-03-201612
20-03-201612
1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft Employee
Microsoft Employee

@Mardin

 

In this scenario, you can add a column to list all dates within the date range between StartDate and EndDate.

 

=List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate])),#duration(1,0,0,0))

66.PNG

 

 

Then expand the lists.

 

67.PNG

 

The dataset will be like:

 

68.PNG

 

Then entire Power Query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM31jdU0oExTYFMQwOlWB24pBlC0hAsa4osCxaCso0MQNJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, #"Daily Average" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Daily Average", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate])),#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Regards,

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Microsoft Employee
Microsoft Employee

@Mardin

 

In this scenario, you can add a column to list all dates within the date range between StartDate and EndDate.

 

=List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate])),#duration(1,0,0,0))

66.PNG

 

 

Then expand the lists.

 

67.PNG

 

The dataset will be like:

 

68.PNG

 

Then entire Power Query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNNM31jdU0oExTYFMQwOlWB24pBlC0hAsa4osCxaCso0MQNJGSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, #"Daily Average" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Daily Average", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([StartDate],Duration.Days(Duration.From([EndDate]-[StartDate])),#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Regards,

 

Wow thanks alot! That did the trick!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.