Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

      

13 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    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's avatar
    Anonymous
    Not applicable
    5 years ago

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

    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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?

         

          

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        hi Anonymous , 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.