Power Query: A List of Distinct Possibilities

Welcome to our Power Query blog. This week, I look at List.Distinct().

I have a list of tent data that I’ve loaded into Power Query:

In another query, I want to only get the distinct values on this list: I can use List.Distinct():

List.Distinct(list as list, optional equationCriteria as any) as list

This returns a list that contains all the values in list with duplicates removed. If the list is empty, the result is an empty list.

My new query looks like this:

Just one minute: I don’t want separate types for ‘Red’ and ‘red’.

There was another parameter in the List.Distinct() formula, and it’s about to prove useful:

List.Distinct(list as list, optional equationCriteria as any) as list

It’s not mentioned on the Microsoft help page, but equationCriteria is going to help me.  It essentially tells Power Query what rules to use when comparing list values.  In my case, I want capital and lowercase letters to match.  I can do this using the function Comparer.FromCulture():

Comparer.FromCulture(culture as text, optional ignoreCase as nullable logical) as function

This returns a comparer function, given the culture and a logical value, ignoreCase, for case sensitivity, for the comparison.  The default value for ignoreCase is FALSE.  The values for culture are well known text representations of locales used in the .NET framework.

I want to switch ignoreCase on.

I now have unique values for my list regardless of letter case.  The M code I have used is:

To keep my list tidy, it might be nice to have a capital letter on all the types: I can do this from the Transform tab, but first I need to convert my list to a table.  The option to do this is in ‘List Tools’:

Now I have a table I can use the Format dropdown in the ‘Text Column’ section of the Transform tab (or by right clicking on the column) to ‘Capitalize Each Word’:

I can then ‘Convert to List’ from the Transform tab:

My list is now ready to use.

Come back next time for more ways to use Power Query!