Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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.
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
hi herbert,
creating a custom column, then using the group by feature works, it's just a lot of steps, compared to being able to create a formula, which is what i really need to do, as i need to do this for more than one table, just using different fields. the formula is easy to update, by changing the potential duplicate field names. having to create a custom column and group by field, table by table, is more than i am willing to do.
i am still baffled, why my original formula will not work anymore.
thank you for your help.
i really appreciate it
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
75 | |
63 | |
62 |
User | Count |
---|---|
141 | |
104 | |
101 | |
80 | |
67 |