cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

@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
Microsoft

@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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.