Power query - pivot 3 columns to 6 columns

[Danny2020]

Hello,

I have a table like this:

Name_1	Name_2	Name_3	Size	Amount _1	Amount_2
			A
			B
			C

How to pivot to show as:

Name 1	Name 2	Name 3	A - Amount 1	B - Amount 1	C - Amount 1	A - Amount 2	B - Amount 2	C - Amount 2

What would be the code to use in power query to accomplish this? I know how to pivot 2 to 3 columns (Size and Amount 1).

Thank you,

[v-yilong-msft]

Hi @Danny2020 ,

I think you can not pivot 3 columns to 6 columns. You can only choose one Amount.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticsWizard]

@Danny2020 

To pivot multiple columns like `Size` and `Amount_1` and `Amount_2` in Power Query, you'll first need to perform an "Unpivot" operation on the `Amount_1` and `Amount_2` columns, followed by a "Pivot" operation on the resulting `Size` column.

Here’s how you can do it step-by-step:

1. Load your data into Power Query.

2. Select the `Amount_1` and `Amount_2` columns.

3. Right-click and select "Unpivot Columns". This will convert these two columns into two rows: one for `Amount_1` and one for `Amount_2`, along with a corresponding value.

4. With the resulting table, you should now have columns named `Attribute` and `Value`. The `Attribute` column will have entries like 'A - Amount 1', 'B - Amount 1', 'A - Amount 2', 'B - Amount 2', etc.

5. Now, select the `Size` column, and in the "Transform" tab, choose "Pivot Column".

6. In the "Pivot Column" dialog, for the "Values Column" select the `Value` column created in step 3.

7. For the "Advanced options" choose "Don't Aggregate" as we assume there's only one record per Name/Size/Amount combination.

Your table should now be pivoted as per your requirement, with a column for each combination of `Size` and `Amount`. Each of these new columns should have the values corresponding to the original rows in your table.

Please note that the exact instructions can vary depending on the layout of your data. If your `Size` column values are already combined with the descriptor (like 'A - Amount 1'), you may not need to perform an "Unpivot" operation first; instead, you could directly pivot the `Size` column.

Here's an example of what the M code might look like for the transformation:

let
Source = [YourSourceHere], // Replace with your actual source step
UnpivotedColumns = Table.UnpivotOtherColumns(Source, {"Name_1", "Name_2", "Name_3"}, "Attribute", "Value"),
PivotedColumn = Table.Pivot(UnpivotedColumns, List.Distinct(UnpivotedColumns[Attribute]), "Attribute", "Value")
in
PivotedColumn

Replace `[YourSourceHere]` with the actual name of your source step.

After applying the steps, be sure to check that the data looks correct and then commit the changes to load the transformed data back into Power BI.