Forum Discussion
PowerQuery Pivot Multiple Columns/Categories
Hi there,
I'm trying to Pivot data in powerquery on MULTIPLE columns to create "sets" of columns.
Below is an image of my data set (unpivoted already) in grey with the final output in green. Notice I am trying to create 4 new columns that are based on 2 columns in the original data set. The categories are Retail and Channel
Any ideas how to accomplish this?
Hi, greenguy2012
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
You may create a new query with the following m codes in 'Advanced Editor'.
let #"Pivoted Column 1" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Month"), Custom1 = Table.Group(#"Pivoted Column 1",{"Region","Sales Rep"},{{"Retail Month",each List.First(List.RemoveNulls([Retail]))},{"Channel Month",each List.First(List.RemoveNulls([Channel]))}}), #"Pivoted Column 2" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Amount", List.Sum), Custom2 = Table.Group(#"Pivoted Column 2",{"Region","Sales Rep"},{{"Retail Sales Amount",each List.First(List.RemoveNulls([Retail]))},{"Channel Sales Amount",each List.First(List.RemoveNulls([Channel]))}}), Res = Table.NestedJoin(Custom1, {"Region", "Sales Rep"}, Custom2, {"Region", "Sales Rep"}, "Res Table", JoinKind.Inner), #"Expanded Res Table" = Table.ExpandTableColumn(Res, "Res Table", {"Retail Sales Amount", "Channel Sales Amount"}, {"Retail Sales Amount", "Channel Sales Amount"}), #"Sorted Rows" = Table.Sort(#"Expanded Res Table",{{"Retail Month", Order.Ascending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Region", "Sales Rep", "Retail Month", "Retail Sales Amount", "Channel Month", "Channel Sales Amount"}) in #"Reordered Columns"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
11 Replies
greenguy2012 What happens when the sales rep has multiple Sales Amounts for multiple different months? What is the desired output then?
Please provide full context for why this must be power query so we can think of best solution, otherwise I advise to keep the data as is, and create measures for:
[Retail Sales Amount] = CALCULATE( SUM(Sales[Sales Amount]), Sales[Category] = "Retail" )
[Channel Sales Amount] = CALCULATE( SUM(Sales[Sales Amount]), Sales[Category] = "Channel" )
Then create a matrix visualization with:
Region, Sales Rep in rows
Sales Month in Columns
[Retail Sales Amount], [Channel Sales Amount] in values
There are lots of little tweaks you can make to this model, but it is a DAX solution, so like i said at beginning, please give the full picture for why it must be Power Query so we can help you better.
Hi Allison,
If there are multiple sales months, then those should be individual records.
The context is for Sales Managers to see this information side by side for additional comparisons (Retail vs Channel)
It needs to be in a table (produced by powerquery) as the managers want to add additional formulas and calculations on top of this output.
Not ideal but it's required of me to produce.
I appreciate the help!
Hi greenguy2012
Can't you already do what is needed by using a pivot table making no changes to the data? If you aren't going to bring the data into PBI and use DAX/Visuals then may as well stick with Excel.
BTW, the green table that is the desired outcome has no data for Feb or Apr?
Sorry to harp on about this, I'm not trying to be difficult. But when I see clients (in our private business) trying to do things the wrong way, part of my job is to point this out and show that there's a better way. Maybe your Sales Managers don't realise what Excel/PBI is capable of ?
regards
Phil
Hi,
Would you be interested in a DAX solution (instead of a Power Query solution)?
Powerquery please!
Hi greenguy2012
Why do you need to do this? The structure you want to change your data into breaks away from the ideal layout that it is in. You are adding columns and introducing nulls.
What is the final aim of this? Can't you do it with the data as it is already structured?
Regards
Phil
Hi Philip, it's a requirement from my stakeholders unfortunately.
Hi greenguy2012
I understand, it's just bad practice. Do your stakeholders really need the data arranged like this? Shouldn't the data stay in the best format to allow easier reporting and not cause issues down the line?
If the aim is to report Retail sales and Channel sales separately, this can already be done with the data as it is.
Regards
Phil
Hi, greenguy2012
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
You may create a new query with the following m codes in 'Advanced Editor'.
let #"Pivoted Column 1" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Month"), Custom1 = Table.Group(#"Pivoted Column 1",{"Region","Sales Rep"},{{"Retail Month",each List.First(List.RemoveNulls([Retail]))},{"Channel Month",each List.First(List.RemoveNulls([Channel]))}}), #"Pivoted Column 2" = Table.Pivot(Table, List.Distinct(Table[Category]), "Category", "Sales Amount", List.Sum), Custom2 = Table.Group(#"Pivoted Column 2",{"Region","Sales Rep"},{{"Retail Sales Amount",each List.First(List.RemoveNulls([Retail]))},{"Channel Sales Amount",each List.First(List.RemoveNulls([Channel]))}}), Res = Table.NestedJoin(Custom1, {"Region", "Sales Rep"}, Custom2, {"Region", "Sales Rep"}, "Res Table", JoinKind.Inner), #"Expanded Res Table" = Table.ExpandTableColumn(Res, "Res Table", {"Retail Sales Amount", "Channel Sales Amount"}, {"Retail Sales Amount", "Channel Sales Amount"}), #"Sorted Rows" = Table.Sort(#"Expanded Res Table",{{"Retail Month", Order.Ascending}}), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Region", "Sales Rep", "Retail Month", "Retail Sales Amount", "Channel Month", "Channel Sales Amount"}) in #"Reordered Columns"Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
