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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors