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,

I am not clear with your expected result.  On a small sample dataset, show the expected result very clearly.

Answer 2

Hi Ashish,

If you see the "data source" sheet tab, the column names below want it unpivot the columns to rows in power query(please see the image belows) . If you see the above image the issue that I'm having is the other columns become all the same values.  I need help where in power query just does the selected columns to rows but doesn't impact the other values to change.If you see the "slicer" sheet tab, I want the slicers to look like that in slicers.

See the

Column name:

Days-d30,d60,d90 columns to rows

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

Arrang: ARR, SETT, SIN columns to rows

Answer 3

Hi,

You may download my Excel file from here.  In the Data Model window, focus on the Data and Data  3 tabs.  I understand that the figures in other columns have been duplicated but i should be able to get your correct desired figures in the Pivot Table with the help of DAX formulas.  So now if you can tell me exactly what calculations you want in the PowerPivot, i can write the DAX formulas for you.  Please be very clear in telling me exactly the figures and layout you want in the PowerPivot so that i can tally my answers with yours.

Answer 4

Hi Ashish,

I just want to unpivot the columns that you see above in the screenshot in power query to rows.  I don't need any calculation since I just need the slicers for the columns that I'm pivoting to rows.  And you could see the screenshots above.  In the below screenshot, this is the issue that arises when I try to unpivot what the columns to rows in power query.  The other column values are repeating the same.

The issues arises when I unpivot the columns to rows below(all the the values that are not highlighted are the same:

Answer 5

The issues arises when I unpivot the columns to rows below(all the the values that are not highlighted are the same:

Well, yes, because if you unpivot d30,d60,d90 you get 3 rows for each row...

If we add an Index column to the main Data table and separate the columns, the issue becomes visible.

The cleared main data without all the columns you want to unpivot is this, note the Index column:

If we separate ARR SETT SIN and Index from the main data and unpivot we end up with this

As you see we have 3 times 0 in the Index column, 3 times 1, etc.

The same appears in d30_d60_d90

In FD_HS_OT_MM we get 4 rows

As all tables have the Index column we can use Power Pivot and create a connection between each:

If we setup a Pivot table we get one row for each item in the main data table and can sum or count the related items in the connected tables.

Means the slicers of the connected tables do not affect the main rows but only there own data. That the reason why we see a count of 2 in this sample:

As you see the values can be summed up for each row, e.g. d30_d60_d90.

Sample file:

https://www.dropbox.com/s/ofol9ozxlihnklq/ec9dbcf6-63ba-4bbe-b1b0-3e12102e23a6.xlsx?dl=1

Is it that what you want to accomplish?

Andreas.