Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.