Error creating duplicate rows formula in query editor

[elz]

Hello,

In excel/power query, i created the following formula to check for duplicate rows, display them and provide a count per row of how many duplicates there are.

let DupRows = ([Address] & " " & [City]) in Table.RowCount (Table.SelectRows(Source, each (([Address] & " " & [City]) = DupRows)))

now, i am trying to create a report in power bi desktop, that uses this table.

the formula generates the following error:

Expression.Error: The field 'Address' of the record wasn't found.
Details:
Name=Products
Data=Table
Item=Products
Kind=Sheet
Hidden=FALSE

the data was imported from an excel spreadsheet into power bi desktop, which has a field named address.

there is a table named products in the power bi desktop file, but it isn't the table that i created the formula in, nor is it from the same data source or have a relationship with the products table, which is why i am stumped.

i'm hoping someone can tell me how to fix the formula.

thank you
Tracy

[v-sihou-msft · Accepted solution]

@elz

In this scenario, you use [Address] to select rows, it will throw error due to circular dependency

For your requirment, please refer to this article: Identify Duplicates Using Power Query. You can also see the Power Query in Advcance Editor.

Regards,