Merge tables with Power Query 0 match when Vlookup returns identical match

[Will_Smith]

I am trying to merge two tables by selecting an identical text column in both of them, I tried all the join kinds, all shows 0 match.

I tested through vlookup, it returns the correct value and text I am looking for.

I still tried  triming the column in both tables, it still shows 0 match when i try to merge.

What should I do to make the power query merge for me?

[NarenM · Accepted solution]

Hi Will - 

The situation you're encountering is not uncommon and can be quite frustrating. Merging tables in Power Query should be straightforward, but sometimes issues like leading/trailing spaces, hidden characters, or mismatched data types can cause problems. Here are somthings that you can try:

1. Check Data Types:

Ensure that the columns you are trying to merge have the same data type in both tables. You can change the data type by selecting the column, then choosing the correct type from the "Data Type" dropdown in the "Transform" tab.

2. Trim and Clean:

You mentioned trimming the columns, but also try using the Clean function to remove non-printing characters.

Select the columns in both tables, then go to the "Transform" tab and choose "Text Column" > "Clean" and then "Trim."

3. Use Fuzzy Matching (Optional):

If there are slight inconsistencies in the text, try using the fuzzy merge option. Click on "Merge Queries" and then "Fuzzy Merge." Adjust the similarity threshold to see if that helps to identify matches.

4. Convert to Text (If Needed):

If the columns contain numbers stored as text in one table and as numbers in the other, convert both to text.

Select the columns, then go to "Transform" > "Data Type" > "Text."

5. Inspect Manually:

If the above steps don't work, take a sample of the data where VLOOKUP is working, and inspect the values manually in Power Query.

Compare the length using the Text.Length function to see if there are any hidden characters.

6. Use a Custom Column (Advanced):

As a last resort, you can create a custom column in both tables that creates a hash or a specific transformation of the column you want to merge. Then, merge the tables based on this custom column.

7. Re-Load the Tables:

If you have made changes to the tables outside of Power Query, make sure to refresh the tables in Power Query to load those changes.

Ensure that the values in the columns you're merging are truly identical, without any hidden differences. Try the query diagnostics in Power Query to see if there's something else going on behind the scenes.