Pivoting multiple columns using PowerQuery in Excel

Question

I'm trying to pivot multiple columns of the following table:

The result I would like to get is the following:

I'm using PowerQuery in Excel, but I couldn't manage to pivot multiple columns (i.e., I can pivot the column "Number", for example). Anyone has any insight about the correct usage of PowerQuery?

Answer 1

Here is an answer to first version of your question

let
    src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lettersABC=List.Distinct(src[Attribute1]),
    count=List.Count(lettersABC),
    lettersNUM=List.Transform({1..count}, each "Letter"&Number.ToText(_)),
    numbersNUM=List.Transform({1..count}, each "Number"&Number.ToText(_)),
    group = Table.Group(src, {"ID"}, {{"attr", each Record.FromList(lettersABC&[Attribute2], lettersNUM&[Attribute1])}}),
    exp = Table.ExpandRecordColumn(group, "attr", lettersNUM&lettersABC, lettersNUM&numbersNUM)
in
    exp

Answer 2

For example, if the country header is in cell A1 then this formula in D2:

= "tax rate" & CountIf( $A$2:$A2, $A2 )

then copy the formula cell D2 and paste it in the cells below it should give you something like:

country tax rate    Income thresholds   count
UK      20%         35k                 tax rate1
UK      30%         50k                 tax rate2
.....

Now you can pivot by that extra count column with PivotTable or PowerQuery. You can use the same formula for the Income th1, Income th2, etc columns.

Answer 3

Here's a solution using the PQ ribbon, but note the last step (Group By) is not dynamic e.g. you would have to change it if you wanted 4+4 columns per country.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"country", type text}, {"tax rate", type number}, {"Income thresholds", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Grouped Rows" = Table.Group(#"Added Index", {"country"}, {{"Min Index", each List.Min([Index]), type number}, {"All Rows", each _, type table}}),
#"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Income thresholds", "Index", "tax rate"}, {"Income thresholds", "Index", "tax rate"}),
#"Added Custom" = Table.AddColumn(#"Expanded All Rows", "Column Index", each [Index] - [Min Index] + 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Min Index", "Index"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Column Index", "Column Index - Copy"),
#"Added Prefix" = Table.TransformColumns(#"Duplicated Column", {{"Column Index", each "tax rate" & Text.From(_, "en-AU"), type text}}),
#"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[#"Column Index"]), "Column Index", "tax rate", List.Max),
#"Added Prefix1" = Table.TransformColumns(#"Pivoted Column", {{"Column Index - Copy", each "Income thresholds" & Text.From(_, "en-AU"), type text}}),
#"Pivoted Column1" = Table.Pivot(#"Added Prefix1", List.Distinct(#"Added Prefix1"[#"Column Index - Copy"]), "Column Index - Copy", "Income thresholds", List.Max),
#"Grouped Rows1" = Table.Group(#"Pivoted Column1", {"country"}, {{"tax rate1", each List.Max([tax rate1]), type number}, {"tax rate2", each List.Max([tax rate2]), type number}, {"tax rate3", each List.Max([tax rate3]), type number}, {"Income th1", each List.Max([Income thresholds1]), type text}, {"Income th2", each List.Max([Income thresholds2]), type text}, {"Income th3", each List.Max([Income thresholds3]), type text}})
in
#"Grouped Rows1"