Forum Discussion

PQ's avatar
PQ
Regular Visitor
2 years ago

Vlookup - in one table based on 3 columns

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 orig...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.