Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
greenguy2012
Advocate I
Advocate I

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?

pivot.PNG

pivot.PNG

 

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @greenguy2012 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

View solution in original post

11 REPLIES 11
v-alq-msft
Community Support
Community Support

Hi, @greenguy2012 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

c1.png

 

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:

c2.png

 

Best Regards

Allan

 

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

AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.

 

Download Excel Workbook

 

pt-res.png

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


The requirement is for the green table unfortunately. I appreciate the help!

Ashish_Mathur
Super User
Super User

Hi,

Would you be interested in a DAX solution (instead of a Power Query solution)?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Powerquery please!

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.