Merge error: "we were unable to determine how many matches the selection will return"

[jadewind]

Hi everyone,

I have been trying to merge a few tables together. There is a main table and there are a few mapping tables which I use to "recode" a few columns in the main table. The end result will be a new main table with all columns recoded to values that can be easily understood. 

While merging the main table with some of the mapping tables, I have this error "we were unable to determine how many matches the selection will return" in the merge screen, and when I click "ok", I get this error:

"An error occurred in the ‘Location’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table"

The interesting thing is, I was able to merge these mapping table before, and from some stage it stopped working. And in the merge screen I can clearly see that the keys match. 

What should I check to find out what went wrong?

Thanks in advance for any suggestions!

[jadewind · Accepted solution]

I figured it out myself....I made some changes in the source data (changed the tab name of the spreadsheet) and Power BI doesn't recognize the change so cannot merge any more. I updated the source data in the mapping tables and now the merge works...

[big4_Analyst]

In my case, I was not accounting for a null scenario in building the value to lookup against. Fixing that is what fixed the error.

Specifically, I was trying to concat null and it was failing.