Downloads
Power Query MTable: rowsIntermediate

Table.Distinct

Removes duplicate rows, or keeps the first row for each distinct value in the columns you name.

Syntax
Table.Distinct(table as table, optional equationCriteria as any) as table

Returns: A table with one row per distinct value.

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

Example

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

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.

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

Things to watch

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.

Related functions

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