Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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:
StartDate | FinishDate | Daily Average |
01-03-2016 | 05-03-2016 | 10 |
06-03-2016 | 15-03-2016 | 15 |
15-03-2016 | 20-03-2016 | 12 |
Final table:
Date | Daily average |
01-03-2016 | 10 |
02-03-2016 | 10 |
03-03-2016 | 10 |
04-03-2016 | 10 |
05-03-2016 | 10 |
06-03-2016 | 15 |
07-03-2016 | 15 |
08-03-2016 | 15 |
09-03-2016 | 15 |
10-03-2016 | 15 |
11-03-2016 | 15 |
12-03-2016 | 15 |
13-03-2016 | 15 |
14-03-2016 | 15 |
15-03-2016 | 15 |
16-03-2016 | 12 |
17-03-2016 | 12 |
18-03-2016 | 12 |
19-03-2016 | 12 |
20-03-2016 | 12 |
Solved! Go to Solution.
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))
Then expand the lists.
The dataset will be like:
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,
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))
Then expand the lists.
The dataset will be like:
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!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |