Share via

Power Query-Pivoting multiple columns to rows

Anonymous
2020-11-21T02:05:43+00:00

HI 

I need help in power query- pivoting selected columns to rows.  In the file below, if you see the data source, some column fields I need for slicers are coming in a column format where I need it rows so I could do slicers.  Whenever I unpivot the selected columns it impacts other columns and I get this below in the attached image where the values are repeating in row.  Basically, if you see the pivot slicer tabs, that is the grouping I need.  this is just a dummy file as example

Grouping that I want to see in the slicers from power query columns:

Days-d30,d60,d90 columns to rows

Brand: FD,HS,MM,OT columns to rows

Arrang: ARR, SETT, SIN columns to rows

date: is already in the row dont need to do anything

PDT: is already in the row don't need to do anything

File Below:

https://1drv.ms/x/s!AmxTHYioikr1lBhoBQu3vOu1v\_zp?e=URvwz9

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

24 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101K Reputation points Volunteer Moderator
    2020-11-22T22:36:52+00:00

    Hi,

    That is bound to happen and i do not think that can be avoided.  We will have to now run some aggregation function either in the Query Editor or the PowerPivot to condense the output that you want.

    0 comments
  2. Anonymous
    2020-11-23T03:26:11+00:00

    Andreas and Ashish-

    I will try it out on the actual data and get back to you shortly.

    0 comments
  3. Anonymous
    2020-11-23T03:26:49+00:00

    Andreas,

    I will try this on the actual data and get back to you right away

    0 comments
  4. Anonymous
    2020-11-26T05:16:46+00:00

    HI Andreas,

    I have 2 questions below after putting it in my actual data.  Basically, I was able follow your screenshots and do the same thing in my actual data and I was able to create the relationship as well.   Please see below the two images.

    1st question: the image below, if I take pdt column and then take a sub group or attribute like below..the values come out to be the same...why is that happening?  Since I want to be able to use all the column fields to manipulate the data

    2nd question: the second image below, I want to take the ratio as shown in image in the pivot table and use it with all attributes or subgroups.  Can you please help me calculate that so it works with all the sub groups and pdt column as well?  The calculation and the columns are same as in the image that needs to be calculated

    1st Image below:

    2nd Image below:

    0 comments
  5. Andreas Killer 144K Reputation points Volunteer Moderator
    2020-11-26T12:19:35+00:00

    No idea what you've done.

    A ratio is the same as a percentage value, to the previous month we can get that if we group the dates by year and month and show the difference (sample file updated):

    It may be possible that your desired result can be achieved with a DAX formula, but DAX is not my specialty.

    Maybe Ashish Mathur can look at that if you share your file.

    Andreas.

    0 comments