Keep Only Duplicate Rows (for auditing) in Power Query M

Keeps only the rows that have at least one other duplicate, for reviewing data quality before removing duplicates.

Row OperationsIntermediateduplicatesauditqualityrows

Power Query M code

let
    Source = YourTable,
    Grouped = Table.Group(Source, {"KeyColumn"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Rows", each _, type table}}),
    OnlyDuplicates = Table.SelectRows(Grouped, each [Count] > 1),
    Expanded = Table.ExpandTableColumn(OnlyDuplicates, "Rows", Table.ColumnNames(Source))
in
    Expanded

Notes

Change KeyColumn to the column, or add more columns to the group-by list, that defines a duplicate in your data.

Requirements

A table where duplicates should be defined by one or more key columns.

How to use this snippet

In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Keep Only Duplicate Rows (for auditing).