Power Query-Pivoting multiple columns to rows

Question

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

Answer 1

Hi ashish,

im sorry you feel that way.  Andreas power query was super helpful and that is what i was looking for but the only issue is when it comes to subgroup all values are coming out the same( my attach image above).   I completely understand your point of view.  I will attach a small subset of data of exactly what i want by tomorrow.  You both are just amazing at what you guys do so thank you for sharing knowledge.

Answer 2

Hi Ashish-

Please see the file below(data source tab and slicer tab).  I have included only 2 months of data to make it easier.  A full month is better than 10-20 to rows to make sure the the data is working to my actual data.  The sheet tab("slicer") is what the outcome should look like where I'm able to do any pivot combinations with these columns and create charts without same values when I sum it.

The exact results I want from this data below:

1. I want the circle columns in the blue ink in the image below-to be rows instead of column names like the orange example the "PDT" column with associated values in the seperate column.   
2. I want to be able to use different pivots and charts with these columns where it sums properly and doesn't bring the same value when I group and subgroup it.

3.   I want the calculation to be done:  Ratio:=DIVIDE([Rec total],[Mnd total of previous month])

4. I want to use the ratio for all the grouping in the blue ink columns with the pdt column and month column as well

UPDATED FILE BELOW:

https://1drv.ms/x/s!AmxTHYioikr1lBxtt4Hfjv0w1ELF?e=7sXn60

Answer 3

Hi,

I have read your question multiple times but still do not understand what you want.  Someone else will help you.

Answer 4

HI Ashish,

Can you please tell me what clarification you need?  Please see the two images below.  The first image is how the data source looks like and the second image is what the outcome should look like so I could do slicers and charts. I need the columns to be unpivot to rows just like the "PDT" column.  I hope this makes sense? 

Answer 5

Hi,

For only the first 2 rows of data, please show the exact result that you want to see in a tabular format.