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

[watje255_ju]

Hello, 

Can someone help me complete the dataset - I would like to have a COGS value for each month and SKU, my current data only has values for some months of the year. Ideally I would like to insert the missing months as new rows, copying all data from the previous month (except month name and start date). 

Is it possible to do this in PowerQuery? @Vijay_A_Verma  I saw you do something similar but couldnt quite replicate the logic 

Link to PBI example file here Extended Table.pbix

Original Data
Product Key	Month & Year	Start of Month	Monthly Average COGS PU	Item Number
1	Jan 2022	1/01/2022	3.239335	SKU 2
1	Feb 2022	1/02/2022	3.464843846	SKU 2
1	Mar 2022	1/03/2022	3.220404	SKU 2
1	May 2022	1/05/2022	3.024761467	SKU 2
1	Jul 2022	1/07/2022	3.228086	SKU 2
1	Dec 2022	1/12/2022	3.376570682	SKU 2
Desired Result
Product Key	Month & Year	Start of Month	Monthly Average COGS PU	Item Number
1	Jan 2022	1/01/2022	3.239335	SKU 2
1	Feb 2022	1/02/2022	3.464843846	SKU 2
1	Mar 2022	1/03/2022	3.220404	SKU 2
1	Apr 2022	1/04/2022	3.220404	SKU 2
1	May 2022	1/05/2022	3.024761467	SKU 2
1	Jun 2022	1/06/2022	3.574447625	SKU 2
1	Jul 2022	1/07/2022	3.228086	SKU 2
1	Aug 2022	1/08/2022	3.228086	SKU 2
1	Sep 2022	1/09/2022	3.228086	SKU 2
1	Oct 2022	1/10/2022	3.228086	SKU 2
1	Nov 2022	1/11/2022	3.228086	SKU 2
1	Dec 2022	1/12/2022	3.376570682	SKU 2

Thanks in advance!

[watje255_ju]

Wow thanks so much!

[Anonymous · Accepted solution]

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.