PowerQuery Pivot Multiple Columns/Categories

[greenguy2012]

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?

[v-alq-msft · Accepted solution]

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.

[AllisonKennedy]

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

[greenguy2012]

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!

[PhilipTreacy]

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

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

[PhilipTreacy]

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

[greenguy2012]

Hi Philip, it's a requirement from my stakeholders unfortunately. 

[PhilipTreacy]

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

[greenguy2012]

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

[Ashish_Mathur]

Hi,

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

[greenguy2012]

Powerquery please!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.