Power Query - Transpose Specific Column into Header Rows While Keeping Group By

Question

I have Data Grouped in Power Query by Vendor and Month, aggregating total spend. I want to Transpose the Month Column such that the intersection of the Vendor row and the Month Column represent the spend by the given vendor in the given month.

How Data Looks:

New_Vendor	MONTH	Amount
VEND1	1/31/2022	1000
VEND1	2/28/2022	900
VEND2	1/31/2022	500
VEND2	2/28/2022	300
VEND2	3/31/2022	1500
VEND3	6/30/2022	600

How I want it to look:

New_Vendor	1/31/2022	2/28/2022	3/31/2022	6/30/2022
VEND1	1000	900	0	0
VEND2	500	300	1500	0
VEND3	0	0	0	600

I have played around with pivoting and unpivoting columns and managed to promote the month column to headers, but the amount column still is its own and all that shows up under the month headers is 1 or a zero:

POWER QUERY PIVOT COLUMN TO ROWS

Here is my M Code before I try to pivot:

let
    Source = Excel.CurrentWorkbook(){[Name="TECH_GL"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Department", type text}, {"Posted Dt.", type datetime}, {"Doc Dt.", type datetime}, {"Doc", type text}, {"Memo / Description", type text}, {"Dep_Num", Int64.Type}, {"Vendor Name", type text}, {"Vendor", type text}, {"JNL", type text}, {"Curr", type text}, {"Txn Amt", type number}, {"Debit (USD)", type number}, {"Credit (USD)", type number}, {"Total", type number}, {"MONTH", type datetime}, {"New_Account", type text}, {"New_Department", type text}, {"New_Vendor", type text}, {"Notes", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"New_Vendor", "MONTH"}, {{"Amount", each List.Sum([Total]), type nullable number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"MONTH", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"New_Vendor", Order.Ascending}, {"MONTH", Order.Ascending}})
in
    #"Sorted Rows"

horseyride · Accepted Answer · 2022-10-19 18:46:30Z

1

You need transform .. pivot column ...

Click select the MONTH column, transform .. pivot column ... choose Amount as the value column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"New_Vendor", type text}, {"MONTH", type date}, {"Amount", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"MONTH", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"MONTH", type text}}, "en-US")[MONTH]), "MONTH", "Amount", List.Sum)
in  #"Pivoted Column"

There is no need for the Group step. As long as you just have the vendor, month and amount columns, pivoting will sum it properly

edited Oct 19, 2022 at 18:46

answered Oct 19, 2022 at 18:26

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jacopo bortolo · Accepted Answer · 2022-10-19 18:27:15Z

0

You can use a simple pivot table. Put the vendor in the "rows", the months in the "column" and the amount in the "value".

answered Oct 19, 2022 at 18:27

jacopo bortolo

11 bronze badge

Comments

horseyride · Accepted Answer · 2022-10-20 23:31:01Z

0

// you can use this one

let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
#"Changed Type" = Table.TransformColumnTypes(Source,{{"New_Vendor", type text}, {"MONTH", type datetime}, {"Am## Heading ##ount", Int64.Type}}), 
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"MONTH", type date}}), 
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"MONTH", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"MONTH", type text}}, "en-US")[MONTH]), "MONTH", "Ammount", List.Sum), 
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"1/31/2022", "2/28/2022", "3/31/2022", "6/30/2022"}) 
in #"Replaced Value"

edited Oct 20, 2022 at 23:31

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

answered Oct 20, 2022 at 21:23

Yassine Erraihani

13 bronze badges

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Power Query - Transpose Specific Column into Header Rows While Keeping Group By

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related