Custom Column Lookup Help

[Anonymous]

Hi all,

I'm currently following this guide (https://community.powerbi.com/t5/Desktop/How-to-lookup-values-in-another-table-in-the-Query-Editor/t...to create a custom column that functions as a lookup for "Standard" in edit query. 

In the example, they use the code below, which works.

(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]

Example in guide ^ 

However when I replicate this with my two tables, it returns errors. Has anyone got a clue why? 

(let currentTaskType = [TaskType] in Table.SelectRows(#"Post and Diary Key", each [TaskType] = currentTaskType)){0}[Standard]

My not working, custom column code ^ 

"Claims Post and Diary" Query

"Claims Post and Diary", where I am adding the custom column ^

"Post and Diary Key" Query

"Post and Diary Key", which contains the "Standard" column I'm trying to replicate ^

Kind regards,

Jordan

[Anonymous · Accepted solution]

Couldn't fix this in the end, had to use a lookup for a custom column outside of query editor instead.

[Anonymous]

File in link @HotChilli @v-eachen-msft 

Thanks for the quick response! 

[HotChilli]

We can't access the source data (that's by design, we don't have access to local files on your system), however, messing around with the data and trying out the M, it looks like the error is 

"There weren't enough elements in the enumeration to complete the operation." or to translate, we can't find this value in the other file.

This will be for 2 reasons, either the value can't be found because there are differences in case (all the TaskType values are Upper Case) or it just doesn't exist in the other table.

[v-eachen-msft]

Hi @Anonymous ,

Could you please share your sample data here if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

[HotChilli]

What's the error?  (Click on the error hyperlink)