List.Distinct
Returns a list with duplicate values removed, keeping one copy of each value.
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
list: the list to check.equationCriteria(optional): how two values are compared. PassComparer.OrdinalIgnoreCaseto treat "Acme" and "ACME" as the same value.
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
- Spaces matter. "Acme Supply" and "Acme Supply " are different values. Trim the text first with Text.Trim.
- Null is treated as one value. Remove nulls first if you do not want a blank in the result.
- Use Comparer.OrdinalIgnoreCase for names and codes typed by people. Use the default for codes where case is part of the value.
- For a table with several columns, use Table.Distinct, which works on rows.
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.