Forum Discussion
Solved
Power Query - Unpivot multiple value groups into multiple value columns?
I have a department that really like to forecast in a tabular format, but they split monthly quantities into two groups of columns: Organic Promotion Country Item Jan Feb Mar Ap...
Thank you both, Ashish_Mathur and lbendlin. For confidentiality I kept my data vague, but your comments pointed me in the right direction.
To help others, here's the generalized explanation of what I did:
- Instead of having "Super Headers" for "Organic" and "Promo", I changed the table so that the monthly headers said "Jan Organic", ... , "Dec Promo"
- I unpivoted all 24 columns together. This gave me 24 rows for each entry (it'll be 12 by the end).
- A new "Attribute" column appears that contains "Jan Organic", ... , "Dec Promo"
- I add a new column "Month" that pulls text before delimiter " " (a single space). This isolates "Jan" thru "Dec".
- I add a new column "Pivot Headers" that pulls text after delimiter " " (a single space). This isolates "Organic" & "Promo"
- I delete the "Attribute" column
- I select "Pivot Headers" and pivot, choosing my value column as... the value column.
In general, you're wanting to unpivot and then re-pivot. The key is to get your desired resulting pivoted headers into a single "Pivot Headers" column. So if you want to end up with 4 groups of values, the "Pivot Headers" needs to have the 4 distinct values.
Hi,
If your data is not very large, then this M code should work
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
#"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{",Country", type text}, {",Item", type text}, {"Organic,Jan", Int64.Type}, {"Organic,Feb", Int64.Type}, {"Organic,Mar", Int64.Type}, {"Organic,Apr", Int64.Type}, {"Organic,May", Int64.Type}, {"Organic,Jun", Int64.Type}, {"Organic,Jul", Int64.Type}, {"Organic,Aug", Int64.Type}, {"Organic,Sep", Int64.Type}, {"Organic,Oct", Int64.Type}, {"Organic,Nov", Int64.Type}, {"Organic,Dec", Int64.Type}, {"Promotion,Jan", Int64.Type}, {"Promotion,Feb", Int64.Type}, {"Promotion,Mar", Int64.Type}, {"Promotion,Apr", Int64.Type}, {"Promotion,May", Int64.Type}, {"Promotion,Jun", Int64.Type}, {"Promotion,Jul", Int64.Type}, {"Promotion,Aug", Int64.Type}, {"Promotion,Sep", Int64.Type}, {"Promotion,Oct", Int64.Type}, {"Promotion,Nov", Int64.Type}, {"Promotion,Dec", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {",Country", ",Item"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"Hope this helps.