Vlookup - in one table based on 3 columns

[PQ]

On my monthly update with thousands of lines from the raw data list I need to replace in Power Query [External Tx ID] "rexxxxxx" (based on "refund" in [Tx Type] column) with "pixxxxxx" (based on original "purchase" in [Tx Type] column), which match as value "Uxxxx" (in [ID] column.

As a result, I need to Add Column with "pixxxxx" value on both lines, means of "purchase" and "refund" line, to be able to further work with it. Please could you help?
Please see more on the snapshot please to better understand my notes above.

[Anonymous]

Hi @PQ ,

Sorry I can't check out exactly what the problem is, can you see if there are any error tips you can provide me with?
Or you can remove the Table.AddColumn at the beginning of the previous code, i.e., the M code becomes like this and try to see if it still reports errors:

= if [Tx Type] = "purchase" then [External Tx ID]
else if [Tx Type] = "refund" then 
let 
currentID = [ID],
relatedPurchase = 
Table.SelectRows(Source, each ([ID] = currentID and [Tx Type] = "purchase"))
in 
if Table.IsEmpty(relatedPurchase) then null else relatedPurchase{0}[External Tx ID]
else null

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PQ]

[PQ]

[PQ]

Hi, thank you for the quick reply. Very much appreciated.

I might do something wrong because I see on your tab it should work, but not for me for some reason, showing error - snapshot below.

[Anonymous]

Hi @PQ ,

You can try this M code:

= Table.AddColumn(Source, "Custom External ID", each 
if [Tx Type] = "purchase" then [External Tx ID]
else if [Tx Type] = "refund" then 
let 
currentID = [ID],
relatedPurchase = 
Table.SelectRows(Source, each ([ID] = currentID and [Tx Type] = "purchase"))
in 
if Table.IsEmpty(relatedPurchase) then null else relatedPurchase{0}[External Tx ID]
else null)

The results are as follows:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.