cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Mcode:Error result If Look Up Value doesnt exist at Source table (should show as blank not "Error")

I have a table that looks up a value from another table namely LookUp table and Review table. I have below MCode in my custom column in Review table.

 

= (let currentLookUp = [UniqueValue] in Table.SelectRows("LookUp", each [UniqueValueCol] = currentLookUp)){0}[CategoryValue])

 

How do I update my formula above so that IF my UniqueValue doesn't exist in Working File - UniqueValueCol column the result in my custom column should be blank rather than Error?

 

I purposely did not use merge thats why I have the above mcode. Any inputs will be appreciated.

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

It takes time to scan so many rows while using Table.SelectRows, please try Table.Group suggested by @CNENFRNL @edhans 

 

you need to put the code offered by @CNENFRNL after your currentLookUp = [UniqueValue] like this and paste in the custom column

=let 
  currentLookUp = [UniqueValue] 
in
  Table.Group("LookUp", "UniqueValueCol", {"Grouped", each _}){[UniqueValue=currentLookUp]}?[Grouped]?{0}?[CategoryValue]?

  

View solution in original post

13 REPLIES 13
edhans
Super User
Super User

If you can share some data we could be of more help @Anonymous . See links below. You are going to have to use Table.Group as @CNENFRNL recommends. It is the only way to get decent performance. See this article by @ImkeF if you are interested in more details. 

 

As you've discovered, you cannot just do an effective VLOOKUP column on a large data set. Power Query bogs down. it isn't designed to work that way. 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hello, I 've tried below, however my table loads really slow? Any ideas on how I can optimize my formula?

 

= (let currentLookUp = try [UniqueValue] in Table.SelectRows("LookUp", each [UniqueValueCol] = currentLookUp)){0}[CategoryValue]) otherwise ""

Hi @Anonymous 

 

have a try

= let 
currentLookUp =  [UniqueValue],
a = Table.SelectRows("LookUp", each [UniqueValueCol] = currentLookUp){0}?,
b= if a = null then "" else a[CategoryValue]
 in 
  b

 

 

Anonymous
Not applicable

hi @Vera_33 , got the same result in my previous formula thanks! However it takes a while to load right after clicking close and load from power query.  I wonder If there is a way to make it much more faster its  like 50 rows per minute and I have 35,000 rows.

Hi @Anonymous 

 

It takes time to scan so many rows while using Table.SelectRows, please try Table.Group suggested by @CNENFRNL @edhans 

 

you need to put the code offered by @CNENFRNL after your currentLookUp = [UniqueValue] like this and paste in the custom column

=let 
  currentLookUp = [UniqueValue] 
in
  Table.Group("LookUp", "UniqueValueCol", {"Grouped", each _}){[UniqueValue=currentLookUp]}?[Grouped]?{0}?[CategoryValue]?

  

Anonymous
Not applicable

Hello @Vera_33 ,

 

Thanks for your solution below 🙂

 

=let

[currentLookUp = [UniqueValue] ,
a=
Table.Group(lookup, "UniqueValueCol", {"Grouped", each _}){[UniqueValueCol=currentLookUp]}?[Grouped]?{0}?,

b= if a = null then "No Category Value" else a[CategoryValue]][b]

 

Anonymous
Not applicable

Hi @Vera_33 Vera,

 

Tried @CNENFRNL solution and yours above however my custom column results is all Error.

Just to understand what is "Grouped" is that a text result? BTW I dont have a Grouped column.

Any thoughts?

 

Hi @Anonymous 

 

Agree with @edhans , you need to provide sample data or the error message, otherwise we can't understand what went wrong in your case.

In my made up data, yes, all text.

"Grouped" is a column when GROUPBY your lookup table, it's a new column contains table - "each _" then you extract the first value - {0}

You are going to have to provide data. Help us help you. Don't ask us to make up data and work on a solution that doesn't fit your needs @Anonymous - we can have 10 more posts discussing theory and ideas and get no where. 

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hi @Vera_33 , @edhans  and , @CNENFRNL 

 

Sharing data in dropbox and uploaded a sample data from thousand of rows.

 

https://www.dropbox.com/sh/i5no71trb9cuxi6/AABJ3nyu6NeWlYQG0ghyMe9Ca?dl=0

 

Let me know your inputs.

 

Anonymous
Not applicable

Hello @Vera_33 , @edhans , and @CNENFRNL 

 

I have corrected the LookUp table, you should be seeing the values now. Thanks @Vera_33 for notifying me.

@Anonymous , an alternative to Table.SelectRows is Table.Group, which is, in general, a bit more efficient.

= Table.Group(#"LookUp", "UniqueValueCol", {"Grouped", each _}){[UniqueValue=currentLookUp]}?[Grouped]?{0}?[CategoryValue]?

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

hi @CNENFRNL ,

 

Tried this one and this goes into error, and text color of "Grouped" is red in my formula bar.

 

Also the result is error, any thoughts?

 

  

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors