cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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 

UserRight 1Right 2Right 3Right 4Right 5
Ayesyesnoyesno
Bnoyesnonono
Cno no yesyesno
Dyesyesnonono

 

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

 

UserRights
ARight 1
ARight 2
ARight 4
BRight 2
CRight 3
CRight 4

 

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

 

Thankx

1 ACCEPTED SOLUTION
Resolver II
Resolver II

Hi,

 

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

So if your data is like this:

BI_Jo_1-1603365384744.png

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.

BI_Jo_2-1603365503248.png

 

I hope that has answered your question!

 

Jo

View solution in original post

1 REPLY 1
Resolver II
Resolver II

Hi,

 

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

So if your data is like this:

BI_Jo_1-1603365384744.png

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.

BI_Jo_2-1603365503248.png

 

I hope that has answered your question!

 

Jo

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors