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

[Anonymous]

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.

[edhans]

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.

[Anonymous]

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 ""

[Vera_33]

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]

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.

[Vera_33 · 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]?

[Anonymous]

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]

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?

[Vera_33]

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}

[edhans]

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.

[Anonymous]

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]

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.

[CNENFRNL]

@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]?

[Anonymous]

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?