Need Some Help with M - How to Use Logic

Hi Shelley,

Error-handling may help, you can try M code like formula below:

#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Distributor APR Long", each (
try
	let 
		currentCustomer = [Sold_To_Party_Description] 
	in 
		Table.SelectRows(Master_APRLongDescription, each [PARTNER] = currentCustomer)){0} [APR_Long_Description] 
otherwise 
	let 
		currentCustomer = [Sold_To_Party_Description] 
	in 
		Table.SelectRows(Master_APRLongDescription, each [PARTNER] = currentCustomer)){0} [Sold_To_APR]
),

Regards,

Jimmy Tao

You can do it like this.

MasterLookup

Select the "Master table" and go to Combine -> Merge Queries

Merge

Setup the merge to be "Left outer"

Setup merge

Expand the Lookup by clicking the top right arrows

Expand Lookup

Add a column with the formula to consider the lookup value if it exists other wise consider a field on the master table:

Lookup formula

Remove unneeded columns and you end up with this:

Final Result