Pivoting multiple columns using PowerQuery in Excel

0
votes

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

enter image description here

The result I would like to get is the following: enter image description here

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?

3
excelpivotpowerquery
The extra columns that you want don't seem to be Pivoted in any way. They just seem like extra columns with the same value on all rows, so you can just add the extra columns with those values after the Pivot. – Slai
What if the extra columns do not have the same values in all rows? – Egodym
Then you need to give a better example and explanation because it is not completely clear what you are trying to achieve. – Slai
You can duplicate the Letter column and do something like in this example thebiccountant.com/2015/08/12/…. – Slai
Example improved! – Egodym

3 Answers

1
votes

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

enter image description here

1
votes

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.

0
votes

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"