Transform [StartDate] and [FinishDate] to table with all dates in between + add average value next

[Mardin]

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

[v-sihou-msft · Accepted solution]

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

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,

[Mardin]

Wow thanks alot! That did the trick!