Power Query VlookUp or another way?

[DanieleX]

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

[DanieleX]

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 · Accepted solution]

@DanieleX,

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:

[DanieleX]

That's solved the issue. Thanks!

[amitchandak]

@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