How to make a Merged Query non case sensitive?

[Anonymous]

Hello

I joined two tables with Merged Query using Left Outer join.

It appears it only matches case-sensitive data and it omits all the non case-sensitive matches.

How can I fix this?

Thanks

[Xcellent101]

You can try to do a fuzzy match when you are doing the Merged Query

https://support.microsoft.com/en-us/office/create-a-fuzzy-match-power-query-ffdd5082-c0c8-4c8e-a794-...

Make sure to set

Similarity Threshold = 1.0 (so you get exact matches)

Ignore Case = Check

As always, make sure to test the matched results before pushing to prod.

Similarity Threshold     Indicates how similar two values need to be in order to match. The minimum value of 0.00 causes all values to match each other. The maximum value of 1.00 only allows exact matches. The default value is 0.80.

Ignore case     Indicates whether text values should be compared in a case sensitive or insensitive manner. The default behavior is case insensitive, which means case is ignored.

[amitchandak]

@Anonymous , Using text-upper, create a new column in both tables and join in Power Query

https://docs.microsoft.com/en-us/powerquery-m/text-upper

[Anonymous]

@amitchandak @az38 

Thanks but I wouldn't like to change the case of the data, is it possible that I introduce a parameter in the Power Query code so that it is non case-sensitive?

[az38]

@Anonymous 

you can try to use solution with "keyEqualityComparers" parameter from here https://community.powerbi.com/t5/Desktop/PowerQuery-Merge-Queries/td-p/149565

But it's not recommended and it's always (from my experience) a bad practice to join by text (especially long case-senisitve text)

[az38]

@Anonymous 

you can execute lowercase or UPPERCASE operation before left join