Power Query - Pivot with multiple values

[LAS]

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

 

[peter789]

UnPivot the data first. Then merge Period column and the column with Amt1 and Amt2 in it. Pivot again.

Peter

 

[alansidman]

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

Data Range
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Account​

[/TD]
[TD]

Branch​

[/TD]
[TD]

Location​

[/TD]
[TD]

Attribute​

[/TD]
[TD]

1/19/2019​

[/TD]
[TD]

2/19/2019​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

12345​

[/TD]
[TD]

A​

[/TD]
[TD]

North​

[/TD]
[TD]

Amount1​

[/TD]
[TD]

100​

[/TD]
[TD]

500​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

12345​

[/TD]
[TD]

A​

[/TD]
[TD]

North​

[/TD]
[TD]

Amount2​

[/TD]
[TD]

150​

[/TD]
[TD]

550​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

12345​

[/TD]
[TD]

A​

[/TD]
[TD]

South​

[/TD]
[TD]

Amount1​

[/TD]
[TD]

200​

[/TD]
[TD]

600​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

12345​

[/TD]
[TD]

A​

[/TD]
[TD]

South​

[/TD]
[TD]

Amount2​

[/TD]
[TD]

250​

[/TD]
[TD]

650​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12345​

[/TD]
[TD]

B​

[/TD]
[TD]

North​

[/TD]
[TD]

Amount1​

[/TD]
[TD]

300​

[/TD]
[TD]

700​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

12345​

[/TD]
[TD]

B​

[/TD]
[TD]

North​

[/TD]
[TD]

Amount2​

[/TD]
[TD]

350​

[/TD]
[TD]

750​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

12345​

[/TD]
[TD]

B​

[/TD]
[TD]

South​

[/TD]
[TD]

Amount1​

[/TD]
[TD]

400​

[/TD]
[TD]

800​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

12345​

[/TD]
[TD]

B​

[/TD]
[TD]

South​

[/TD]
[TD]

Amount2​

[/TD]
[TD]

450​

[/TD]
[TD]

850​

[/TD]
[/TR]
</tbody>[/TABLE]

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

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]H[/td]
[td="bgcolor:#ECF0F0, align:center"]I[/td]
[td="bgcolor:#ECF0F0, align:center"]J[/td]
[td="bgcolor:#ECF0F0, align:center"]K[/td]
[td="bgcolor:#ECF0F0, align:center"]L[/td]
[td="bgcolor:#ECF0F0, align:center"]M[/td]
[td="bgcolor:#ECF0F0, align:center"]N[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Attribute[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Values[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Amount1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Amount2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Account[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Branch[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Location[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of 1/19/2019[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of 2/19/2019[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of 1/19/2019[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Sum of 2/19/2019[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]12345[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]North[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]100[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]500[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]150[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]550[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]South[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]200[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]600[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]250[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]650[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]North[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]300[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]700[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]350[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]750[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"][/td]
[td="bgcolor:#FFFFFF, align:CENTER"]South[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]400[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]800[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]450[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]850[/td]
[/tr]
[/table]