Create lines for missing months in Dataset, copying lines from most recent result

Hi watje255_ju ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2)Create a new query.

= Record.FromList(List.Repeat({0},12), {"1".."9", "10","11","12"})

(3) Click "Advanced Editor" in power query and copy and paste the following code. You can check the steps on the right hand side step bar.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY+9CsJAEAZfJVwdkv273bUXiwQrsQopoqQTCyGFb29A4RbObooZvt1pSpjaNCzPhoBoR+wB+x9zR3xgzjtexmtDaW6//mm9BZ+KLyou7KJVcl5eIeEwQSAgf/x38HPxgcQURa1Khu0REosTDl6fdFzvxcfwBZtmA3UqyfwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Key" = _t, #"Month & Year" = _t, #"Start of Month" = _t, #"Monthly Average COGS PU" = _t, #"Item Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Key", Int64.Type}, {"Month & Year", type date}, {"Start of Month", type date}, {"Monthly Average COGS PU", type number}, {"Item Number", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Month & Year", type text}}, "en-US"), "Month & Year", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Month & Year.1", "Month & Year.2", "Month & Year.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Month & Year.1", type text}, {"Month & Year.2", Int64.Type}, {"Month & Year.3", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Month & Year.3", "year"}, {"Month & Year.1", "month"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"Product Key", "year","Item Number"}, {"all", each Record.ToTable(Query1&Record.FromList([Monthly Average COGS PU],[month]))}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Name", "Value"}, {"all.Name", "all.Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded all",{"Product Key", "year", "all.Name", "all.Value", "Item Number"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",0,null,Replacer.ReplaceValue,{"all.Value"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"all.Value"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Filled Down",{{"all.Name", "month"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "day", each 1),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"year", type text}, {"day", type text}}, "en-US"),{"year", "month", "day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Merged"),
    #"Renamed Columns2" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Start of Month"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Start of Month", type date}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type2", "Start of Month", "Start of Month - Copy"),
    #"Renamed Columns3" = Table.RenameColumns(#"Duplicated Column1",{{"Start of Month - Copy", "Month & Year"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns3",{"Product Key", "Month & Year", "Start of Month", "all.Value", "Item Number"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Reordered Columns1",{{"all.Value", "Monthly Average COGS PU"}})
in
    #"Renamed Columns4"

(4) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.