Vlookup - in one table based on 3 columns

Question

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 · Answer

Hi&nbsp;PQ&nbsp;,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 TaoIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pq · Answer

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.

pq · Answer

&nbsp;

pq · Answer

&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;PQ&nbsp;,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