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

     

     

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

      Anonymous Hello, Thank you for your input. I have not had time to try it. My reply was meant for Jimmy. Somebody marked that reply as a solution and it didn't work for me. Other priorities have pulled me away from this right now. When I work on it again, I will give this a try. Thanks again for your help!

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Shelley

        If the solution you're talking about is the one I posted, I'm interested in finding out what didn't work for you.

         

        It would have been much easier and faster to solve if you had published an example with the situation you wanted to see solved.

        You can find a link here to a pbix with my understanding of your request.
         https://www.dropbox.com/s/3nj7t3c5hwc9cvh/Lookup%20example.pbix?dl=0

         

        Please let me know if I'm missing something.