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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
otis_pc
Frequent Visitor

Merge not working despite identical value

I have two tables. Call the first #"Extracted Data" and the second #"Name Variations". 

 

Extracted Data

SourcePerson LeadLengthEncoded(merged) Employee ID
Swiss CowsDoe, Michael R.15068111101044032077105099104097101108032082046null
DuckDuckGoBuck, Jason A.14066117099107044032074097115111110032065046null
BraveFawn, Mercy H.1407009711911004403207710111409912103207204656942

 

Name Variations

Employee IDName CategoryValueLengthEncoded
1234LFMDoe, Michael R.15068111101044032077105099104097101108032082046
1234FLMichael Doe11077105099104097101108032068111101
987LFMBuck, Jason A.14066117099107044032074097115111110032065046
987FMLJason A. Buck13074097115111110032065046032066117099107
56942LFMFawn, Mercy H.14070097119110044032077101114099121032072046
56942LogonFAWNMH6070065087078077072

 

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.

3 REPLIES 3
edhans
Super User
Super User

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.

edhans_0-1681336917786.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

 

nbsp with hash.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors