cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors