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 ,

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.