<!-- https://learnsql.cpetoday.com/functions/m/table-distinct -->
# Table.Distinct

Language: Power Query M
Category: Table: rows
Syntax: Table.Distinct(table as table, optional equationCriteria as any) as table
Summary: Removes duplicate rows, or keeps the first row for each distinct value in the columns you name.
Returns: A table with one row per distinct value.
Level: Intermediate
Related: Table.Group, Table.RemoveMatchingRows, Table.Sort, Table.Buffer

## What it does
Table.Distinct keeps one row for each distinct value. With a list of column names it keeps the first row found for each combination of those columns. Without a list it removes rows that are identical in every column. Duplicate invoice numbers, repeated payment lines and a unique customer list all come from this step.

## Syntax and arguments
- **table**: the table to clean. Required.
- **equationCriteria**: optional. A list of column names keeps one row for each distinct combination of those columns. Leave it out to compare every column.

## Example
An invoice list was exported twice, so some invoice numbers appear two times. The version with the latest update should be kept.

```powerquery
let
    Source = Excel.CurrentWorkbook(){[Name = "Invoices"]}[Content],
    Sorted = Table.Buffer(Table.Sort(Source, {{"LastUpdated", Order.Descending}})),
    OnePerInvoice = Table.Distinct(Sorted, {"InvoiceNumber"})
in
    OnePerInvoice
```

The table is sorted so the latest update comes first. Table.Distinct then keeps the first row it finds for each invoice number, which is the latest version. The Table.Buffer step fixes the sorted order in memory, so the kept row is the one you sorted to the top.

## Example: a second, different use
A payment file may contain identical lines pasted twice. Removing exact duplicate rows needs no column list.

```powerquery
let
    Source = Excel.CurrentWorkbook(){[Name = "Payments"]}[Content],
    NoExactDuplicates = Table.Distinct(Source)
in
    NoExactDuplicates
```

## Things to watch
- Duplicate invoice numbers are not always errors. A credit memo and an invoice can share a number, or two customers can use the same number. Check the other columns first.
- A full-row duplicate check misses rows that differ in only one column, such as a changed amount. Use a column list when you need one row per key.
- Table.Buffer keeps the sorted order, but it holds the table in memory, so use it for small and medium tables.
- Query folding: Table.Distinct on a column list does not fold reliably. Check View Native Query.

## In business terms
Duplicate invoices and duplicate payments are a common control finding. A query that lists the duplicates, and keeps one row for the rest, gives the reviewer evidence of how the problem was handled.
