Pivoting

[YasminYas]

Sample data attached

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

INPUT 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 meOutput should be like above

[Anonymous]

Just wondering if this helped you out?

[Anonymous · Accepted solution]

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:

Here's the excel file:

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

-Nick