Table.Distinct
Removes duplicate rows: whole rows, or rows that repeat the values of the columns you name.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Distinct(table as table, optional equationCriteria as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to clean. |
equationCriteria | any optional | The columns that decide what a duplicate is, as a name or a list of names, each possibly with a comparer: {"Product", Comparer.OrdinalIgnoreCase}; every column when left out. |
Returns
table. The first row of each set of duplicates, in the table's order.
Remarks
- Texts are compared exactly unless a comparer says otherwise: "Chair" and "chair" are two values.
Examples
Whole rows
Table.Distinct(#table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}, {"Chair", 50}}))| Product | Price |
|---|---|
| Chair | 50 |
| Desk | 120 |
One row per product
Table.Distinct(#table({"Shop", "Product", "Units"}, {{"Oslo", "Chair", 4}, {"Bergen", "Desk", 1}, {"Oslo", "Lamp", 6}, {"Bergen", "Chair", 2}}), {"Product"})| Shop | Product | Units |
|---|---|---|
| Oslo | Chair | 4 |
| Bergen | Desk | 1 |
| Oslo | Lamp | 6 |
The first row of each product is kept.
Ignoring case
Table.Distinct(#table({"Product"}, {{"Chair"}, {"chair"}, {"Desk"}}), {"Product", Comparer.OrdinalIgnoreCase})| Product |
|---|
| Chair |
| Desk |