Forum Discussion
Mcode:Error result If Look Up Value doesnt exist at Source table (should show as blank not "Error")
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]?
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
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.
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]?Hi Anonymous 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?
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.
Hello Anonymous ,
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]
