Forum Discussion

maracles's avatar
maracles
Resolver II
10 years ago
Solved

Help needed with unpivot Power Query Transform

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 (blu...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    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's avatar
maracles
Resolver II
10 years ago

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

ImkeF's avatar
ImkeF
Community Champion
10 years ago

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's avatar
    maracles
    Resolver II
    10 years ago

    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.