Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Will_Smith
Regular Visitor

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

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?

 

 

1 ACCEPTED SOLUTION
NarenM
New Member

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.

View solution in original post

1 REPLY 1
NarenM
New Member

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors