Advanced unpivoting multiple columns

[squarecat]

Hello, I have this source table 

		PLAN	PLAN	PLAN	ACT	ACT	ACT	LY	LY	LY
ID	Cost Center	Jan	Feb	Mar	Jan	Feb	Mar	Jan	Feb	Mar
123	A	100	90	120	90	110	80	60	150	75
234	B	50	65	40	40	55	70	35	60	80
345	C	75	80	85	85	20	50	55	80	65
567	D	60	40	80	40	80	65	85	50	60

And I need it to be in this format

ID	Cost Center	Date	PLAN	ACT	LY
123	A	1/1/2025	100	90	60
234	B	1/1/2025	50	40	35
345	C	1/1/2025	75	85	55
567	D	1/1/2025	60	40	85
123	A	1/2/2025	90	110	150
234	B	1/2/2025	65	55	60
345	C	1/2/2025	80	20	80
567	D	1/2/2025	40	80	50
123	A	1/3/2025	120	80	75
234	B	1/3/2025	40	70	80
345	C	1/3/2025	85	50	65
567	D	1/3/2025	80	65	60

Can someone guide me into how to achieve this result?

[lbendlin]

Your source pivot table is not in a usable format, especially taking into account that it will likely get wider through the course of the year.  See if you can get it in a more suitable format.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4/C8IwEMW/SsncIf8uiWNNEZQqDi4SMlToWkH7/fEuJqno4nC/vHfw3iUE1rA2zXnoTj9P5y9fHK4fiG1g+x61vz+Xxk/zMj3QHcYZuZtuyOP434aqhFR0B0dwjtwQhFylIDqCSR6IFlJYKo1mi5O2BhCaFwBZS0pBiWMRBZWmjX835XoHBek61AqXuykIxqLpS52un1uVqT2Qfx3jCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    Months = {1..(List.Count(Table.ColumnNames(Source))-2)/3},
    #"Converted to Table" = Table.FromList(Months, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Columns", each {"Column1","Column2"} & List.Transform({[Column1]*3..[Column1]*3+2},each "Column" & Text.From(_))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Data", each let s = Table.SelectColumns(Source,[Columns]) in Table.AddColumn(Table.PromoteHeaders(Table.Skip(s,1), [PromoteAllScalars=true]), "Type", each List.Max(Record.ToList(s{0})))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"ID", "Cost Center", "Jan", "Feb", "Mar", "Type"}, {"ID", "Cost Center", "Jan", "Feb", "Mar", "Type"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Data", {"ID", "Cost Center", "Type"}, "Month", "Value")
in
    #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

As you can see your months are sorted alphabetically. Your data model should include a calendar table.

[jgeddes · Accepted solution]

Starting with data formatted like...

You can end up with...

with the following code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4/C8IwEMW/SsncIf8uiWNNEZQqDi4SMlToWkH7/fEuJqno4nC/vHfw3iUE1rA2zXnoTj9P5y9fHK4fiG1g+x61vz+Xxk/zMj3QHcYZuZtuyOP434aqhFR0B0dwjtwQhFylIDqCSR6IFlJYKo1mi5O2BhCaFwBZS0pBiWMRBZWmjX835XoHBek61AqXuykIxqLpS52un1uVqT2Qfx3jCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    new_columns = 
    List.Transform(List.Zip({Record.ToList(Table.FirstN(Source, 2){1}), Record.ToList(Table.FirstN(Source, 2){0})}), each if _{1} = " " then _{0} else Text.Combine(_, "-")),
    rename_list = 
    List.Zip({List.Generate(()=>1, each _ <= List.Count(new_columns), each _ + 1, each "Column" & Text.From(_)), new_columns}),
    edit_table = 
    Table.RenameColumns(Table.Skip(Source, 2), rename_list),
    unpivot_other_columns = 
    Table.UnpivotOtherColumns(edit_table, {"ID", "Cost Center"}, "Attribute", "Value"),
    split_attribute_column = 
    Table.SplitColumn(unpivot_other_columns, "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Date", "Type"}),
    pivot_type_column = 
    Table.Pivot(split_attribute_column, List.Distinct(split_attribute_column[Type]), "Type", "Value"),
    transform_date = 
    Table.TransformColumns(pivot_type_column,{{"Date", each Date.FromText("01"&_&"2025"), type date}}),
    table_sort = 
    Table.Buffer(Table.Sort(transform_date, {{"Date", Order.Ascending}, {"ID", Order.Ascending}, {"Cost Center", Order.Ascending}}))
in
    table_sort

[squarecat]

Hello! thanks for the help, if it's not too much trouble could you explain how the code works so I can understand better? 😊

[jgeddes]

Sure.
The Source step is the sample data. You would have your source data here instead.

new_columns = 
    List.Transform(List.Zip({Record.ToList(Table.FirstN(Source, 2){1}), Record.ToList(Table.FirstN(Source, 2){0})}), each if _{1} = " " then _{0} else Text.Combine(_, "-")),

A few things going on here. The idea is to take the first two rows of the table and turn them into a single header that can be used in a renaming columns step. So the Table.FirstN() function is used to get the first two rows from the Source table. Record.ToList(Table.FirstN(Source, 2){1}) gets the record values from the second of the two first rows (Power Query is zero indexed) and turns them into a list. The same is done for the values in the first row, the {0}. List.Zip() turns those two lists into a single list of lists. The List.Transform() iterates through the list and combines (Text.Combine()) the two list values with "-" as long as the value from the second row (of the current iteration) is not " ". The conditional logic is what prevents the first two columns from being "ID-" and "Cost Center-" .

Now we have a list of what we want our column names to be.

rename_list = 
    List.Zip({List.Generate(()=>1, each _ <= List.Count(new_columns), each _ + 1, each "Column" & Text.From(_)), new_columns}),

This step creates the list that will be used in the rename columns step. 

First, the List.Generate() function creates a list starting at one and ending at the length of the list we created in the new_columns step. This list is formatted as "Column1", "Column2"... etc. 

The "Column" list is zipped to the new_columns list to create the renaming list. 

This step is built this way to allow for the table headers to grow in number over time when new months are added. 

    edit_table = 
    Table.RenameColumns(Table.Skip(Source, 2), rename_list),

The first two rows of the Source table are removed. (We do not need them as they are just the header info that we have captured already.)

The resulting table has the columns renamed using the rename_list that we created. 

unpivot_other_columns = 
    Table.UnpivotOtherColumns(edit_table, {"ID", "Cost Center"}, "Attribute", "Value"),

Now we unpivot the table using the first two columns as the anchor. This allows for the table to expand with additional months without breaking the query.

split_attribute_column = 
    Table.SplitColumn(unpivot_other_columns, "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Date", "Type"}),

This simply splits the "Attribute" column we just created. Of note is that the delimiter here must match the delimiter that is used in the new_columns step. Also, the "Date" and "Type" column names are set here.

pivot_type_column = 
    Table.Pivot(split_attribute_column, List.Distinct(split_attribute_column[Type]), "Type", "Value"),

Pivots the "Type" column using the Value column with no aggregation to get the values.

transform_date = 
    Table.TransformColumns(pivot_type_column,{{"Date", each Date.FromText("01"&_&"2025"), type date}}),

Turns the month abbreviation into the first of the month in 2025 using the Date.FromText() function.

table_sort = 
    Table.Buffer(Table.Sort(transform_date, {{"Date", Order.Ascending}, {"ID", Order.Ascending}, {"Cost Center", Order.Ascending}}))

Sorts the table. And you end up with the desired result.

[squarecat]

This is brilliant, thanks for the help! it worked like a charm 👌 and your explanation was very clear