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
Thanks Imke for the response.
I've tried following the stps and have got as far as splitting the "Attribute" column which leaves me with the image below:
The rest of the steps don't seem to work though, I'm not sure how to replace 'Attribute 2' by Amount as there are no null values.
Thanks.
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