Forum Discussion

elz's avatar
elz
Advocate II
10 years ago
Solved

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] & ...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    elz

     

    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

  • KGrice's avatar
    KGrice
    10 years ago

    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.

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
10 years ago

elz

 

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

  • elz's avatar
    elz
    Advocate II
    10 years ago
    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's avatar
      KGrice
      Memorable Member
      10 years ago

      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.

  • elz's avatar
    elz
    Advocate II
    10 years ago

    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

    • v-haibl-msft's avatar
      v-haibl-msft
      Microsoft Employee
      10 years ago

      elz

       

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

       

      1. Add a custom combined column.
      2. Click “Group By” in Transform tab and specify the columns as below.
      3. Expand the Detail with checking Address and City.
      4. 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

      • elz's avatar
        elz
        Advocate II
        9 years ago

        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