Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elz
Advocate II
Advocate II

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

2 ACCEPTED SOLUTIONS
v-haibl-msft
Employee
Employee

@elz

 

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

Problem Error creating duplicate rows formula in query editor_1.jpg

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.

Problem Error creating duplicate rows formula in query editor_2.jpg

 

Best Regards,

Herbert

View solution in original post

KGrice
Memorable Member
Memorable Member

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.

View solution in original post

7 REPLIES 7
v-haibl-msft
Employee
Employee

@elz

 

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

Problem Error creating duplicate rows formula in query editor_1.jpg

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.

Problem Error creating duplicate rows formula in query editor_2.jpg

 

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

@elz

 

I’m still using my previous table here. Let’s try to do it in Query Editor.

 

  1. Add a custom combined column.
    Problem Error creating duplicate rows formula in query editor_1.jpg
  2. Click “Group By” in Transform tab and specify the columns as below.
    Problem Error creating duplicate rows formula in query editor_2.jpg
  3. Expand the Detail with checking Address and City.
    Problem Error creating duplicate rows formula in query editor_3.jpg
  4. Delete the Custom column created in first step.
    Problem Error creating duplicate rows formula in query editor_4.jpg

 

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 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
KGrice
Memorable Member
Memorable Member

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.