Forum Discussion
Problem/Error creating duplicate rows formula in query editor
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.
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
thank you for the dax formula. i would like to be able to create the formula in the power query window, so that i can delete the duplicate records, when needed. i will try to translate your formula in the query editor.
thank you
tracy
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.