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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors