Automated Column Creation Based on Month & Copying Month Values to YTG Months

[Anonymous]

Hi, 

I have a product table. In this table i have product names and prices of the producst in actual months.

For example for "Product1" , i have jan, feb, march and april price columns. Next month, May price column will be added to this sheet.

I want to find an automated way to create columns for next months and copy current month's (april) values to these columns, i want to create a full year prices table. Is there a way to handle this with Power Query or M ? 

[dax · Accepted solution]

Hi @Anonymous , 

You could try below M code to see whether it works or not

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0AhFAbGoM4cXqRCslAZnGIAmwJEjW2AQskQxkmpiCCJgKUzOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, Jan = _t, Feb = _t, Mar = _t, Apr = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"name"}, {{"all", each _, type table [name=text, Attribute=text, Value=number, Custom=list]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Dates(#date(2020,1,1),365,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "month", each Text.Start(Date.MonthName([Custom]),3)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"month", "name"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Removed Duplicates", "all", {"Attribute", "Value"}, {"Attribute", "Value"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded all", "Custom", each if [Attribute]=[month] then [Value] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Attribute", "Value"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"name", "month"}, {{"max", each List.Max([Custom]), type number}}),
    #"Filled Down" = Table.FillDown(#"Grouped Rows1",{"max"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[month]), "month", "max", List.Sum)
in
    #"Pivoted Column"

Best Regards,
Zoe Zhi

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