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 current table.

 

So, I have the lookup as follows:

 

//Lookup APR Long Name in Master Data View Table
#"Added Custom3" = Table.AddColumn(#"Filtered Rows1", "Distributor APR Long", each (let currentCustomer = [Sold_To_Party_Description] in Table.SelectRows(Master_APRLongDescription, each [PARTNER] = currentCustomer)){0} [APR_Long_Description]),

 

How do I add the logic that if the [APR_Long_Description] field is missing or null, to then use the field [Sold_To_APR] in the current table?

 

Thanks!

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

     

     

9 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    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

    • Shelley's avatar
      Shelley
      Post Prodigy
      7 years ago

      I tried this exactly as written and it did not work; therefore, it should not be marked as a solution.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Shelley,

        Can you be more specific when you say it doesn't work?
        I tried to put screen shots of all the steps.
        If you publish your teste scenario I'll look at it.

    • Shelley's avatar
      Shelley
      Post Prodigy
      7 years ago

      v-yuta-msft Hello Jimmy, This should not be marked as a solution. I tried this exactly as written and it did not work. I had some errors. I don't remember exactly what happened now and do not have time at the moment to work on it again.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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