Conditional column to use another value in the table

[ruthhacche]

The third item in this table was transferred from the second item. I need a new column or a way of extracting my original code such that:

If TransferID = 0 then use ItemCode Else return the code associated with the TransferID. So on Row 3 use the ItemCode a1002 (which has ID 7291) rather than a1003 but on all other rows just use the item code. This must be so easy for someone.

 

[pjmorris]

1. Create a new query 'From Table' in Powerquery.
2. Create a custom column that uses ItemID if TransferID is null or TransferID if not null.
3. Create a Duplicate Query.
4. Merge Tables as New (thats my preference).
5. Do a Left (Outer) Join between the tables using the TransferID and ItemID.
6. Expand the resulting table retaining just the new ItemCode column.
7. Delete the custom column created in Step 2.

Hope that helps.

 

[ruthhacche]

Thank you

 

[pjmorris]

Delighted to have helped