Power Query - Unpivot multiple value groups into multiple value columns?

[Funk-E-Guy]

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	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
US	TESTITEM	100	100	100	100	100	100	100	100	150	200	300	200	0	0	0	50	0	0	100	0	0	0	200	0

And I am looking for a way to unpivot this data so that I I have this result:

Country	Item	Organic	Promo
US	TESTITEM	100	0
US	TESTITEM	100	0
US	TESTITEM	100	0
US	TESTITEM	100	50
US	TESTITEM	100	0
US	TESTITEM	100	0
US	TESTITEM	100	100
US	TESTITEM	100	0
US	TESTITEM	150	0
US	TESTITEM	200	0
US	TESTITEM	300	200
US	TESTITEM	200	0

I can only seem to unpivot this into 24 rows, instead of 12 rows and 2 value columns.

[Funk-E-Guy · Accepted solution]

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:

1. Instead of having "Super Headers" for "Organic" and "Promo", I changed the table so that the monthly headers said "Jan Organic", ... , "Dec Promo"
2. I unpivoted all 24 columns together. This gave me 24 rows for each entry (it'll be 12 by the end).
3. A new "Attribute" column appears that contains "Jan Organic", ... , "Dec Promo"
4. I add a new column "Month" that pulls text before delimiter " " (a single space). This isolates "Jan" thru "Dec".
5. I add a new column "Pivot Headers" that pulls text after delimiter " " (a single space). This isolates "Organic" & "Promo"
6. I delete the "Attribute" column
7. 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.

[Ashish_Mathur]

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.

[lbendlin]

In your curent form your data is not usable as it would result in duplicate column names which Power Query then butchers.

Can you please check and post the actual raw data format? Maybe more like this?

// Table (2)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSAWP/ovTEvMxkIIsgCijKz80vyczPI6w4VidayTm/NK+kqBLI9SxJzQVSXokgnW6pSUDSN7EISDoWFIHZIEVepXlgMgckXpoOJINTC0AOTC4Bkn75ZUDSJTWZauaAnBgaDOSEuAaHeIa4+gKZhgYGpJGmINIIzDY2QLCRsSkyxxBDGqYhNhYA", 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, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t])
in
    Source