Forum Discussion
Help needed with unpivot Power Query Transform
Check your column "OrderID"
Unpivot others
Split your column "Attribute" by space at the rightmost delimiter
Check column "Attribute.2"
Replace null by Amount
Pivot on this column
Sorry, you need to split by length instead: 14
Thanks, I've followed that step to get the below results but still cannot get the data in the format I need even after trying to do the pivot as suggested. Sorry, I feel really useless not being able to work this out (not used Pivot/Unpivot before)!
You're nearly there :-)
Now check the column "Attribute.2" and pivot on it.
Choose "Value" for the Value and no aggregates in the advanced options.
ImkeF Almost there! That gets me to this... how do I then merge the rows so that the 'Amount' and 'Date' for a particular Transaction are on the same row?
I'll try and solve it myself but thought I'd get the answer now in case you're not around if I fail!
Thanks for the help, the Pivot/Unpivot feature looks like it could be very useful.
Hi maracles,
don't feel yourself useless! you just need to follow these easy steps :
1) Get your table
2) Select the OrderID column and Unpivot the other columns
3) Select the Attribute column and split it by delimiter just like in the screenshot
4) Replace the null values
5) Pivot over the Attribute.3 column
6) After that you can delete the unwanted columns and obtain your result.
Let me know if it works.
#I'M Not An Expert#
Anonymous
Thank you very much for the effort and useful diagrams. I ahve tried following those steps but when I pivot the Attribute 2 column in the way you describe I don't get the table that you show afterwards, instead I get this:
Any ideas?
Thanks.Thanks, I got it to work using your 'Split Columns' and using a value of 3 instead of 14, and then continuing from there. Much appreciated.