Forum Discussion

greenguy2012's avatar
greenguy2012
Advocate I
5 years ago
Solved

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....
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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's avatar
AllisonKennedy
Community Champion
5 years ago

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's avatar
greenguy2012
Advocate I
5 years ago

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's avatar
    PhilipTreacy
    Super User
    5 years ago

    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