Forum Discussion

Shelley's avatar
Shelley
Post Prodigy
7 years ago
Solved

Need Some Help with M - How to Use Logic

Hi All,   I want to lookup one field in another table in the query editor and then grab a field from that table; however, if there's no corresponding record, I want to use a static field in the cur...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    7 years ago

    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

     

     

Shelley's avatar
Shelley
Post Prodigy
7 years ago

Anonymous I know it would've been easier if I could post a sample, but my file is confidential and it is huge, so to make something small enough and open enough to share would take me a very long time. I wish it was easier because it would be easier for me to find help and

get the work done at the same time. Thanks again for your help. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Shelley wrote:

Anonymous I know it would've been easier if I could post a sample, but my file is confidential and it is huge, so to make something small enough and open enough to share would take me a very long time. I wish it was easier because it would be easier for me to find help and

get the work done at the same time. Thanks again for your help. 

Hi Shelley,

Have you managed to check again the solution I proposed? To save you time, last week I created and posted an example pbix file. 
Your feedback is important.