Forum Discussion
Power Query VlookUp or another way?
Hello all,
I'm pretty new to PowerBi, I'm looking to manipulate the data in Power Query to have a result where the Order-ID will be my unique id to put into relation with other tables.
I have this initial data sample with the amounts for the same order split into multiple lines depending on the descriptions.
| order-id | amount-type | amount-description | amount |
| 302-8280724-3255530 | ItemPrice | Principal | 172,12 |
| 302-8280724-3255530 | ItemPrice | Tax | 32,7 |
| 302-8280724-3255530 | ItemFees | Commission | -21,43 |
| 305-1931380-0225167 | ItemPrice | Principal | 106,55 |
| 305-1931380-0225167 | ItemPrice | Tax | 20,24 |
| 305-1931380-0225167 | ItemFees | Commission | -14,41 |
| 305-3602247-3812336 | ItemPrice | Principal | 57,38 |
| 305-3602247-3812336 | ItemPrice | Tax | 12,05 |
| 305-3602247-3812336 | ItemFees | Commission | -9,25 |
The result I'm looking for is something like this:
| order-id | amount-type | Principal Ammount | Tax Ammount | Commission Ammount |
| 302-8280724-3255530 | ItemPrice | 172,12 | 32,7 | -21,43 |
| 305-1931380-0225167 | ItemPrice | 106,55 | 20,24 | -14,41 |
| 305-3602247-3812336 | ItemPrice | 57,38 | 12,05 | -9,25 |
Thanks for your help
I believe the reason "Commission" is showing up on a different line, is because the AmountType is different.
In Power Query, use the ReplaceValues to change ItemFees to ItemPrice
Then Pivot your AmountType
Then Pivot AmountDescription
#"Replaced Value" = Table.ReplaceValue(Source,"ItemFees","ItemPrice",Replacer.ReplaceText,{"amount-type"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"amount-type"]), "amount-type", "amount"), #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[#"amount-description"]), "amount-description", "ItemPrice") in #"Pivoted Column1"Using these steps in PQ, I get the following result. I hope this is what you are looking for:
4 Replies
- amitchandak
Super User
DanieleX , Unpivot last 2 columns
https://radacad.com/pivot-and-unpivot-with-power-bi
Unpivot Data(Power Query) https://www.youtube.com/watch?v=2HjkBtxSM0g&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=11 Hello Amitchandak,
Thank you for your answer.
Unpivot on amount-description & amount column doesn't create a result that I need.
I tried Pivot, without aggregate that it almost give me my result.
The only problem is that Commission is on a different line- rsbin
Community Champion
I believe the reason "Commission" is showing up on a different line, is because the AmountType is different.
In Power Query, use the ReplaceValues to change ItemFees to ItemPrice
Then Pivot your AmountType
Then Pivot AmountDescription
#"Replaced Value" = Table.ReplaceValue(Source,"ItemFees","ItemPrice",Replacer.ReplaceText,{"amount-type"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[#"amount-type"]), "amount-type", "amount"), #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[#"amount-description"]), "amount-description", "ItemPrice") in #"Pivoted Column1"Using these steps in PQ, I get the following result. I hope this is what you are looking for:
That's solved the issue. Thanks!
