List.Unique is missing: 2 easy to use alternatives

With all the functions in Power Query (over 700), it’s hard to believe there are any missing. However, there is no function for List.Unique.

I know what the clever ones amongst you will say… “It’s called List.Distinct”.

The thing is, unique and distinct are not the same. So, while List.Distinct exists, there is no List.Unique function.

Table of Contents:

Difference between unique and distinct
Simulating List.Unique with custom functions
Applying the custom functions
Wrap-up

Difference between unique and distinct

Let’s start by looking at the difference between unique and distinct.

Let’s say we have a list in Power Query as follows:

{"Alpha", "Bravo", "Charlie", "Alpha", "Delta", "Bravo"}

The distinct list from those values would provide one instance of each item. To calculate that, we use the List.Distinct function.

List.Distinct Function:

=List.Distinct({"Alpha", "Bravo", "Charlie", "Alpha", "Delta", "Bravo"})

Alpha and Bravo appear twice in the list, so the duplicate instances of each are removed.

List.Distinct Returns {“Alpha”, “Bravo”, “Charlie”, “Delta”}

But, if Alpha and Bravo appear twice, those values are not unique. A unique list should contain items that appear once, and only once. Therefore, the result would be as follows:

Unique Result: {“Charlie”, “Delta”}

While we may use the terms distinct and unique interchangeably, they are not the same.

Distinct: one instance of each item
Unique: items with only one instance

This is even more confusing if you’ve come from the Excel world. The syntax for the UNIQUE function is:

=UNIQUE(array, [by_col], [exactly_once])

The default setting of Excel’s UNIQUE function returns a distinct list (i.e., one instance of each item from the array). But the last argument, [exactly_once], toggles between unique (if true) or distinct (if false or omitted).

Therefore, the UNIQUE function in Excel returns both unique and distinct arrays.

There is no equivalent in Power Query. Hopefully, you’ll agree that List.Unique is missing from Power Query’s function library.

Simulating List.Unique with custom functions

Since List.Unique is missing; I want to provide you with multiple custom functions to simulate the result.

List.Unique custom function #1

The first method is a combination of other list functions:

List.Distinct creates a list of distinct items
List.Difference combined with List.Distinct creates a list of all the non-unique items
Using List.RemoveMatchingItems with a list of non-unique items leaves only the unique items

I must give thanks to Mahmoud Bani Asadi (https://www.linkedin.com/in/softwaretrain/) for showing this method to me. It is by far the easiest to apply and understand.

The code for the custom function is:

(List as list) =>
  let
    UniqueList = List.RemoveMatchingItems(
      List, List.Difference(
        List, List.Distinct(List)
        )
      )
  in
    UniqueList

I have called this function fxListUnique1.

Syntax

The syntax of the function is as follows:

=fxListUnique1(List as list) as list

The arguments are:

List: The source list from which the list of unique items is derived.

List.Unique custom function #2

The second method:

Creates a distinct list from the original list
For each item in the distinct list, counts the occurrences in the original list
Where there is only one occurrence, the value is added to a new list
The new list is returned, which is a list of unique items

The code for the custom function is:

(List as list) as list =>
  let

    //Buffer the list to prevent errors and increase speed
    BufferList = List.Buffer(List),

    //Obtain a distinct list
    DistinctList = List.Distinct(BufferList),

    //Loop through the distinct list and identify if unique
    UniqueList = List.Accumulate(
      List.Numbers(0, List.Count(DistinctList)), 
      {}, 
      (state, current) =>
        if List.Count(List.Select(BufferList,
          each _ = DistinctList{current})) = 1 then
          List.Combine({state, {DistinctList{current}}})
        else
          state
    )
  in
    UniqueList

I have called this function fxListUnique2.

Syntax

The syntax of the function is as follows:

=fxListUnique2(List as list) as list

The arguments are:

List: The source list from which the list of unique items is derived.

Notes

As this is a looping function, it can be slow on very large lists.

List.Unique custom function (Method 3)

The third method:

Converts the list to a Table
Performs a count using the Group By transformation
Filters to include items with only one occurrence
Converts the Table back to a list
The result is a list of unique items

The code for the custom function is:

(List as list) as list =>
  let
 
    //Convert the list to a Table
    convertToTable = Table.FromList(
      List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    //Perform GroupBy transformation using the count aggregator
    groupCount = Table.Group(
      convertToTable, 
      {"Column1"}, 
      {{"Count", each Table.RowCount(_), Int64.Type}}
    ), 

    //Filter the count for items containing one item
    filterUnique = Table.SelectRows(groupCount, each ([Count] = 1)),

    //Convert the Table back to a list
    uniqueList = Table.SelectColumns(filterUnique, {"Column1"})[Column1]
  in
    uniqueList

I have called this function fxListUnique3.

Syntax

The syntax of the function is identical to Method #1 above:

=fxListUnique3(List as list) as list

The arguments are:

List: The source list from which the list of unique items is derived.

Applying the custom functions

To apply either custom function, the method is the same.

List.Unique function simulated with a custom function

Using the example at the start of the post, we could call the function as follows:

= fxListUnique1({"Alpha", "Bravo", "Charlie", "Alpha", "Delta", "Bravo"})

Returns: {“Charlie”, “Delta”}

Wrap-up

Unique lists contain items appearing only once in a source list, while a distinct list includes one instance of each item.

Power Query does not have a List.Unique function to return a unique list. However, we can simulate the result of this function using custom functions in multiple ways.

Other Resources

Looking for more custom functions or want to know more about distinct lists? Check out these resources: