Forum Discussion
Merge not working despite identical value
I have two tables. Call the first #"Extracted Data" and the second #"Name Variations".
Extracted Data
| Source | Person Lead | Length | Encoded | (merged) Employee ID |
| Swiss Cows | Doe, Michael R. | 15 | 068111101044032077105099104097101108032082046 | null |
| DuckDuckGo | Buck, Jason A. | 14 | 066117099107044032074097115111110032065046 | null |
| Brave | Fawn, Mercy H. | 14 | 070097119110044032077101114099121032072046 | 56942 |
Name Variations
| Employee ID | Name Category | Value | Length | Encoded |
| 1234 | LFM | Doe, Michael R. | 15 | 068111101044032077105099104097101108032082046 |
| 1234 | FL | Michael Doe | 11 | 077105099104097101108032068111101 |
| 987 | LFM | Buck, Jason A. | 14 | 066117099107044032074097115111110032065046 |
| 987 | FML | Jason A. Buck | 13 | 074097115111110032065046032066117099107 |
| 56942 | LFM | Fawn, Mercy H. | 14 | 070097119110044032077101114099121032072046 |
| 56942 | Logon | FAWNMH | 6 | 070065087078077072 |
I am merging #"Name Variations" into #"Extracted Data" on [Person Lead] = [Value] and pulling in the [Employee ID] column.
[Person Lead] and [Value] in both tables have been cleaned and trimmed.
The merge works as expected except for two values: 'Doe, Michael R.' and 'Buck, Jason A.' which both return null for the [Employee ID] instead of their actual employee ids when merged on [Person Lead] = [Value].
If I copy 'Doe, Michael R.' directly out of the #"Extracted Data" table and filter the [Person Lead] column in #"Name Variations" by pasting that value in, #"Name Variations" returns the row I need, so the merge should work since the values are equal, but it doesn't.
Troubleshooting steps:
- visual inspection is identical
- length of text in both tables is identical
- selecting only {"A".."Z", "a".."z", ",", " ", "."} into a new column in both tables and merging on that works
- Using Power Query “M” To Encode Text As Numbers – Paul Turley's SQL Server BI Blog into a new column and merging on that works
Everything in the troubleshooting steps comes back that merge should work: that is, the length is the same in both tables, the text is identical, the tables merge correctly using the Text.Select column, and the tables merge correctly using the encoded value.
But the value remains null after the merge on the original column pair of [Person Lead] = [Value].
Any ideas? I'm hoping for a 'duh' moment.
3 Replies
If you can copy from Extracted Data into Name Variations and it works, but doesn't until you do that, they are not the same values. One of the most insidious causes of this is the non-breaking space, ASCII 160 vs the normal space char of ASCII 32. You cannot see it unless you have a text editor like NotePad++ and turn on visibility to all chars.
If that is what it is, or similar, you need to copy that char to the clipboard, then on that column, do Transform, Replace Values, and paste that into the Value to Find box and type in a simple space in the Replace With box.
Another alternative is to do a fuzzy merge. The default is .8, so turn it up to .9 or even .95 and see if it will merge without any find/replace.The function that encodes text as numbers returns 032 for both spaces in each of the names. I tested that function with a nbsp, and it returns 160, so that isn't it. Good thinking, though.
Fuzzy matching isn't an option for this dataset.
You are going to have to share data via Dropbox from a text file then or some other way. Pasting in the forum can strip the info out.
