Problem/Error creating duplicate rows formula in query editor

elz

To display the duplicate rows and count, you can also do it with DAX. I assume we have a simple table like below.

We can create a new column with following formula.

Duplicate = 
CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[City] = EARLIER ( 'Table'[City] )
                && 'Table'[Address] = EARLIER ( 'Table'[Address] )
        )
)

Then drag a table chart into your canvas, apply a visual level filter: Duplicate is greater than 1.

Best Regards,

Herbert

Hi elz. If you just want to use it to remove duplicate records in the query editor, do you need to count the rows? It looks like you could add a new column that only concatenates City and Address, then remove duplicates from the new column, and finally delete the column.

To do this using the UI, select the City and Address columns and select Merge Columns in the Add Column tab of the ribbon. Select the new column, then from the Home tab in the ribbon, use the Remove Rows dropdown to select Remove Duplicates. Then delete the new column.