Forum Discussion
Power Query - Unpivot multiple value groups into multiple value columns?
- 1 year ago
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.
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.