cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
YasminYas Regular Visitor
Regular Visitor

Pivoting

Sample data attached

https://www.dropbox.com/s/rki3lhmh6augvqk/Sample%20data.xls?dl=0

N1.PNGINPUT data Has same data for each user except Gruoup name so I need to show each row for each row with all the group associated with each user..I have tried pivoting ..But its not worked for meN2.PNGOutput should be like above

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Pivoting

Usually it's the other way around ( want to get the data into rows..) so this was good challenge.  

 

Everything was down in PQ, so you will be able to walk through the applied steps and see what is happening.  There is 1 Table, 2 Lists and 3 functions. There is a tab in the excel file called  "Column Names".  It has the original column names and the new column names. Since you wanted theer to be new rows, the Ans columns all cannot be 1 and two ( else they would append together) so I changed the column names to be able to input a number.  Both of those tables in excel ( old column names and the new column names) are loaded as lists into PQ with the appropriate name.

 

But from there all the steps are in the FinalTable. Way to much going there to type all out here. This will not be the most rebust thing (for instance fnFillDown and fnFillUp are hard-coded with column names) but this took a little bit of time and didnt want to take all the fun for myself  🙂

 

Looks like it is pretty spot on with your desired output:

Final Table Comp.png

 

Here's the excel file:

https://1drv.ms/x/s!AoQIGRpzoxRHgbUhTG24JBBjirVNfw

 

-Nick

 

 

 

View solution in original post

2 REPLIES 2
Super User I
Super User I

Re: Pivoting

Usually it's the other way around ( want to get the data into rows..) so this was good challenge.  

 

Everything was down in PQ, so you will be able to walk through the applied steps and see what is happening.  There is 1 Table, 2 Lists and 3 functions. There is a tab in the excel file called  "Column Names".  It has the original column names and the new column names. Since you wanted theer to be new rows, the Ans columns all cannot be 1 and two ( else they would append together) so I changed the column names to be able to input a number.  Both of those tables in excel ( old column names and the new column names) are loaded as lists into PQ with the appropriate name.

 

But from there all the steps are in the FinalTable. Way to much going there to type all out here. This will not be the most rebust thing (for instance fnFillDown and fnFillUp are hard-coded with column names) but this took a little bit of time and didnt want to take all the fun for myself  🙂

 

Looks like it is pretty spot on with your desired output:

Final Table Comp.png

 

Here's the excel file:

https://1drv.ms/x/s!AoQIGRpzoxRHgbUhTG24JBBjirVNfw

 

-Nick

 

 

 

View solution in original post

Super User I
Super User I

Re: Pivoting

Just wondering if this helped you out?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors