Forum Discussion
Problem/Error creating duplicate rows formula in query editor
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
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.
7 Replies
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 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
tracyHi 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.
hi herbet,
i was able to use your formula in dax.
i have tried numerous ways to get your formula to work in the query editor, but had no luck. i understand that there are differences between the m language and dax, which is where i am getting stuck.
i still don't understand why the formula (that i posted in my original message) i used in power query , in the query editor, will not work in the query editor in power bi desktop.
do you have any idea why that is?
any help in getting this formula to work in the query editor would be greatly appreciated.
thank you
tracy
I’m still using my previous table here. Let’s try to do it in Query Editor.
- Add a custom combined column.
- Click “Group By” in Transform tab and specify the columns as below.
- Expand the Detail with checking Address and City.
- Delete the Custom column created in first step.
Following is the Power Query in Advanced Editor.
let Source = Excel.Workbook(File.Contents("C: \ProblemError creating duplicate rows formula in query editor.xlsx"), null, true), Table_Sheet = Source{[Item="Table",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table_Sheet,{{"Column1", type text}, {"Column2", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each [City] & [Address]), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Duplicate", each Table.RowCount(_), type number}, {"Detail", each _, type table}}), #"Expanded Detail" = Table.ExpandTableColumn(#"Grouped Rows", "Detail", {"Address", "City"}, {"Address", "City"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Detail",{"Custom"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"City", "Address", "Duplicate"}) in #"Reordered Columns"Best Regards,
Herbert
- Add a custom combined column.
