Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!