Fuzzy Merge ignores spaces after special characters

[TPS]

PowerBI and also Excel's Fuzzy Merge will ignore spaces after a special character even if the similarity threshold is set to 1 and should only ignore capitalization.

As seen in the following example from Excel it maches all rows except for the second one since there's a space between "SECOND" and "row".

But the space between "With-" and "Space" is ignored and it still matches with "with- space".

This problem also occurs with other special characters aside from minus.

The versions used are:
Power BI Report Server (on premise) Version1.14.8179.37378 (May 2022)
Excel 365 Version 2108 (Build 14326.20962)

[v-cazheng-msft]

Hi @TPS,

Since “second” and “row” in the source2 are glued together, Power BI will treat it as an entire text part. But for the “SECOND row”, they will be treated as two text parts: SECOND and row. That’s why they are not matched.

Here is a sample can help you understand the working principal.

If you would like to make the “SECOND row” match with “secondrow”, you could merge like this: delete 1 from Similarity threshold(optional) input box and tick Match by combining text parts.

Match by combining text parts option will try to combine text parts such as “Micro” and “soft” into “Microsoft” to find matches. But the 1 Similarity will only allow exact matches which will prevent the match between “SECOND row” and “secondrow”.

Best Regards,

Community Support Team _ Caiyun

[TPS]

Hello @v-cazheng-msft,

sorry my explanation was a bit confusing.

The bug I'm trying to report is about the last row "With- space" that matches with "with-space" even though the second entry does not have a space. 

I only included other examples to say that in normal cases the space is not being ignored (as it shouldn't).

Regards,

TPS