Skip to content

List.Unique is missing: 2 easy to use alternatives

List.Unique in Power Query

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

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 from Power Query

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:

  • Crates 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 =>

    //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}}})

I have called this function fxListUnique1.


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.


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

List.Unique custom function (Method 2)

The second 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 =>
    //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(
      {{"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]

I have called this function fxListUnique2.


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

=fxListUnique2(List as list) as list

The arguments are:

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


  • This method is significantly faster than Method #1, as it does not loop through each item.

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”}


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.

Other Resources

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