Power Query M and case sensitivity

In my last post (Filtering on multiple columns using one slicer) one of the steps I took was to remove duplicate entries from a list. The M language that Power Query and Power BI use is case sensitive. This means if you try and remove duplicates from a list and you have the same text but with different capitalisation, it will keep both of these entries when you try and use remove duplicates. This can cause some issues so lets look at how to fix it.

To the Query Editor NoStRoMo, Nostromo and nostromo will be unique values.

The rest of Power BI is not case sensitive so it will consider these to be the same. When you try and make a relationship between tables using this data, it will say it is invalid as one table doesn’t contain unique values.

There is a way around this though. When you have have used the remove duplicates function, click back on the remove duplicates step that has just been created and you should see this in your formula bar:

= List.Distinct(Source)

Simply change it to say the below:

= List.Distinct(Source,Comparer.OrdinalIgnoreCase)

This works with many functions that compare values such as Table.Distinct and Text.Contains.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s