Table.FuzzyNestedJoin: Ignore.Space = true not working with numbers

[xChillout]

Space between String and Number or Numbers does not seem to be ignored. The following examples wont "fuzzy-match" even with Ignore.Space=true:
- "M365" and "M 365"
- "Excel365" and "Excel 365"
- "12345" and "123 45"

does anyone have a workaround for this?

[v-kpoloju-msft · Accepted solution]

Hi @xChillout,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @lbendlin  for your inputs on this issue.

After reviewing the details you provided, I have identified few workarounds that may help resolve the issue. Please follow these steps:

Thanks for the additional details. You're right. Ignore.Space=true should work to match strings like "M365" and "M 365," but it seems that there might be some limitations with how spaces are handled between numbers and text in the current fuzzy matching logic. This behaviour appears to not fully align with what you'd expect, and there might be issues when spaces are between numbers (e.g., "123 45" vs. "12345").

Preprocess Columns to Remove Spaces: It is advisable to clean your columns by removing spaces between numbers and text before performing the fuzzy match. You can achieve this by creating a custom column that removes spaces, ensuring that "M 365" becomes "M365" prior to the fuzzy join:

Table.AddColumn(Source, "CleanedColumn", each Text.Replace(Text.From([YourColumn]), " ", ""))

You can apply this logic to both text and number-based columns to ensure they match more consistently.

Handling Text vs. Numbers Separately: If numbers are still causing mismatches, try separating them from the text in your columns. This way, you can apply fuzzy matching only to the text part, avoiding mismatches caused by spaces in numeric values.

Verify that the Threshold is set appropriately (too low or too high might impact the results). You can try additional preprocessing in Power Query, like replacing spaces manually or converting the columns to a uniform format (e.g., removing spaces from both columns before the join) as a workaround.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[xChillout]

Hi @v-kpoloju-msft thanks a lot for your detailed reply and confirming that

Table.FuzzyNestedJoin() does not work as expected/intended in the described scenario.

[lbendlin]

They mostly refer to trailing spaces.

Use Table.AddColumn with a custom column generator where you can implement whatever join logic you want.

[xChillout]

thanks for engaging @lbendlin. trailing spaces are for sure a possible scenario - eventhough one could and should trimm those beforehand.
The official function documentation gives the following example: [...] when true,"Gra pes" is matched with "Grapes".

Based on that I get the impression that the expected behaviour of the function is to also match "M 365" with "M365". At least I can't find any indication for an exclusion of numbers. 

I guess your proposal would mean for me to (re-)build and improve the FuzzyNestedJoin function. I appreciate the idea and will think about whether and when to go that way.