I just read a blog by Matt Allington on how to fill gaps in data in Power Query. I’ve also had this issue and found another solution using a different approach. In stead of creating a new date table and joining the original data to that I create a new column with a list of date where the value applies and expand that. You do need some experience using the advanced editor.
There are three tricks I use here. You can see both in highlighted in the code below.
The first one is that you can Merge a query to itself. I’ve added two index columns to the table to be able to find the next available date.
The second one is that I create a list of dates in a new column.
The third one is that I can expand my new column with a list of dates to generate the new records.
Copy and paste the below code in a blank query using the advanced editor and you can see the different steps in action
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjTSNdE1MjC0VNJRMjRQitUBCpkihCzAIkbGSIqMlGJjAQ==”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Qty = _t]),
#”Changed Type” = Table.TransformColumnTypes(Source,{{“Date”, type date}, {“Qty”, Int64.Type}}),
#”Sorted Rows” = Table.Sort(#”Changed Type”,{{“Date”, Order.Ascending}}),
#”Added Index” = Table.AddIndexColumn(#”Sorted Rows”, “Index”, 1, 1),
#”Added Previous Index” = Table.AddIndexColumn(#”Added Index”, “PrevIndex”, 0, 1),
#”Merged 2 Queries internally” = Table.NestedJoin(#”Added Previous Index”, {“Index”}, #”Added Previous Index”, {“PrevIndex”}, “Table1 (2)”, JoinKind.LeftOuter),
#”Expanded Table with next Date” = Table.ExpandTableColumn(#”Merged 2 Queries internally”, “Table1 (2)”, {“Date”}, {“Next Date”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded Table with next Date”,{{“Date”, “Previous Date”}}),
#”Added DurationDays” = Table.AddColumn(#”Renamed Columns”, “DurationDays”, each if Duration.Days(Duration.From([Next Date]-[Previous Date])) is null then 1 else Duration.Days(Duration.From([Next Date]-[Previous Date]))),
#”Added New Date column” = Table.AddColumn(#”Added DurationDays”, “Date”, each List.Dates([Previous Date], [DurationDays], #duration(1,0,0,0))),
#”Expanded New Date Column” = Table.ExpandListColumn(#”Added New Date column”, “Date”),
#”Removed Columns” = Table.RemoveColumns(#”Expanded New Date Column”,{“Previous Date”, “Index”, “PrevIndex”, “Next Date”, “DurationDays”}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{“Date”, type date}}),
#”Reordered Columns” = Table.ReorderColumns(#”Changed Type1″,{“Date”, “Qty”})
in
#”Reordered Columns”