Power Query - Pivot with multiple values

LAS

Board Regular
Joined
Oct 29, 2002
Messages
215
Hi All,

Does anyone know (in power query) how to Pivot data where 2 fields would appear in the value area eg:
Columns would effectively be made up of Period and the 2 amount fields.

Source Data:
Account.Branch…….Location……….Period.............Amt1………........Amt2
12345...........A...........North...........Jan-19...........100..............150
12345...........A...........South...........Jan-19...........200..............250
12345...........B...........North...........Jan-19...........300..............350
12345...........B...........South...........Jan-19...........400..............450
12345...........A...........North...........Feb-19...........500..............550
12345...........A...........South...........Feb-19...........600..............650
12345...........B...........North...........Feb-19...........700..............750
12345...........B...........South...........Feb-19...........800..............850



Result.Data:
Account.Branch..Location….Jan-19-Amt1........Jan-19-Amt2….......…Feb-19-Amt1…........Feb-19-Amt2
12345…….A……….North .........100....................150.......................500…....................550
12345…….A……….South .........200....................250.......................600.......................650
12345…….B…….…North .........300....................350.......................700.......................750
12345…….B……….South .........400....................450.......................800.......................850
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
UnPivot the data first. Then merge Period column and the column with Amt1 and Amt2 in it. Pivot again.

Peter
 
Upvote 0
If this presentation works for you, then use the Mcode shown beneath it in Power Query

Data Range
A
B
C
D
E
F
1
Account​
Branch​
Location​
Attribute​
1/19/2019​
2/19/2019​
2
12345​
A​
North​
Amount1​
100​
500​
3
12345​
A​
North​
Amount2​
150​
550​
4
12345​
A​
South​
Amount1​
200​
600​
5
12345​
A​
South​
Amount2​
250​
650​
6
12345​
B​
North​
Amount1​
300​
700​
7
12345​
B​
North​
Amount2​
350​
750​
8
12345​
B​
South​
Amount1​
400​
800​
9
12345​
B​
South​
Amount2​
450​
850​

<tbody>
</tbody>

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account", "Branch", "Location", "Period"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Period", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Period", type text}}, "en-US")[Period]), "Period", "Value")
in
    #"Pivoted Column"

Once Closed and Loaded to an Excel sheet you can then pivot the data to look like the following

vHIJKLMN
3AttributeValues
4Amount1Amount2
5AccountBranchLocationSum of 1/19/2019Sum of 2/19/2019Sum of 1/19/2019Sum of 2/19/2019
612345ANorth100500150550
7South200600250650
8BNorth300700350750
9South400800450850
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top