Help needed with unpivot Power Query Transform

[maracles]

I've attached a screenshot which I think explains everything. I have a data table (green) which I need to transpose/unpivot using power query (Get & Transfrom) so that I get the table seen below (blue). I'm pretty sure I need to use unpivot but can't get it to work quite how I need it to. 

Thanks. 

[ImkeF · Accepted solution]

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

[maracles]

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. 

[ImkeF]

Sorry, you need to split by length instead: 14

[maracles]

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)!

[Anonymous]

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#

[maracles]

@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. 

[maracles]

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. 

[ImkeF]

Sorry, yes - the changed split-operation meant that a blank space has sneaked in. So the other solution would have been to Trim the Attribute.1-column before pivoting.

[Anonymous]

3) Select the Attribute column and split it by delimiter just like in the screenshot

4) Replace the null values

[Anonymous]

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

 

[ImkeF]

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.

[maracles]

@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.