Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Shelley
Post Prodigy
Post Prodigy

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!

2 ACCEPTED SOLUTIONS
v-yuta-msft
Community Support
Community Support

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

View solution in original post

Anonymous
Not applicable

You can do it like this.

MasterMasterLookupLookup

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

MergeMerge

Setup the merge to be "Left outer"

Setup mergeSetup merge

Expand the Lookup by clicking the top right arrows

Expand LookupExpand 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 formulaLookup formula

 

Remove unneeded columns and you end up with this:

Final ResultFinal Result

 

 

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

You can do it like this.

MasterMasterLookupLookup

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

MergeMerge

Setup the merge to be "Left outer"

Setup mergeSetup merge

Expand the Lookup by clicking the top right arrows

Expand LookupExpand 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 formulaLookup formula

 

Remove unneeded columns and you end up with this:

Final ResultFinal Result

 

 

v-yuta-msft
Community Support
Community Support

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

@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.

 

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

Anonymous
Not applicable

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.

@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
Not applicable

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

 

lu.JPG

Please let me know if I'm missing something.

 

@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
Not applicable


@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.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.