Downloads
Power Query MList: transform and filterBeginner

List.Distinct

Returns a list with duplicate values removed, keeping one copy of each value.

Syntax
List.Distinct(list, optional equationCriteria)

Returns: A list of the unique values.

What it does

List.Distinct removes repeated values from a list. Use it to get the unique customers, vendors, account codes or periods in a column, or to see how many different values exist before you build a report. By default, values must match exactly, including capital letters.

Syntax and arguments

Example

A controller wants the list of distinct vendor names on this month's bills, so that each vendor can be checked against the vendor master file. Some names were typed in different capitals.

let
    VendorNames = {"Acme Supply", "ACME SUPPLY", "Bolt Hardware", "Acme Supply"},
    Unique = List.Distinct(VendorNames, Comparer.OrdinalIgnoreCase)
in
    Unique

The result keeps one copy of "Acme Supply" and one of "Bolt Hardware". Without the comparer, "ACME SUPPLY" would stay as a separate value, which is why the comparer matters for names entered by hand.

Example: a second, different use

Counting distinct customers in a column from a table: List.Count(List.Distinct(Invoices[customerName])).

Things to watch

In business terms

Distinct lists are the start of vendor master checks, customer counts, duplicate payment screens and any "how many different X" question. They are quick to build and easy to explain to an auditor.

Related functions

All Power Query M functions · View as Markdown · M basics