How to best transform my data in Power Query from yes/no columns to rows (SAP rights and roles)

[BobKoenen]

HI All,

I have multiple excel files that make up a rights and roles set up from an SAP software installation. I am trying to make an interactive dashboard but am truggiling to transform the data so I can create a datamodel. 

My data looks like this this 

User	Right 1	Right 2	Right 3	Right 4	Right 5
A	yes	yes	no	yes	no
B	no	yes	no	no	no
C	no	no	yes	yes	no
D	yes	yes	no	no	no

to get to a searchable transactiontable I would like the data to be like this:

User	Rights
A	Right 1
A	Right 2
A	Right 4
B	Right 2
C	Right 3
C	Right 4

Ik am struggeling to transform the data to this, I hope you can give me some good tips. 

Thankx

[BI_Jo · Accepted solution]

Hi,

Sounds like you need to unpivot the data in Power Query Editor:

So if your data is like this:

You select the columns so they appear like the image above and use the 'unpivot' button in the 'transform' menu.  This will then give you a table as below, where I've then filtered out the values with a no.  You can delete the value column after the filter if you don't need it.

I hope that has answered your question!

Jo