Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Selectively remove duplicate rows?

In the query editor, there is a function which allows a developer to remove duplicate rows based on the selected columns.   I want to add an additional condition to this.  That is, I want to choose...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago

    Power Query, if I'm reading my own pbix right where I have a similar situation (have appended a new report to an old one, and if there's a matching case number I want to keep the data from the new report), removes duplicates from the bottom up - if you make a custom column that gives anything that's type A the value 1, and type B the value 2, then sort ascending on the new column, this should force it to remove the rows you want

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

Hi Anonymous,

After research and test, there is a way to view the rows moved using Query Statement.

You create a copy table of your resource table, remove the duplicate rows in your original table. Then merge the original table( have removed the duplicate rows) to the copy table using inner join. Then you can filtered the merged table to get all removed rows.

For example, my Sample table is named 'Table1', I create a copy table 'Copy_Table1'. Remove the duplicate rows in Table1 based on [Area], then merge it to Copy_Table1. Filter Copy_Table1 based on [Copy_Table1.Record Number]=[Table1.Record Number], eventually delete the unnecessay columns, you will get the expected result. The following is my statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdO7EoJADIXhV2G2piBZdoFSe8dCO4bCC63MqBS+vZBjg01OkSKTr/snfR8klOE0P+6XT1kcb+/pOj4LSWWhlTTLabeMaBjKPigho8lIyNpkTchkMhEym8yEbEyu62H6k3krW5MtITuTnS+1MikVQQV0rXSex9fWNluLTKKMRSiJjEUqcVrtV4pW4sQyilji1DKKWrZ7FLmkJSh6SefTiGDqBDOKYCoE/b2VEhS5NBIUtbR2yppFLk2MRS/NjF2CDV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Record Number" = _t, #"Starting Date" = _t, Area = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Record Number", Int64.Type}, {"Starting Date", type date}, {"Area", type text}, {"Count", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Area"},Table1,{"Area"},"Table1",JoinKind.Inner),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Record Number", "Starting Date", "Area", "Count"}, {"Table1.Record Number", "Table1.Starting Date", "Table1.Area", "Table1.Count"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Custom", each if [Record Number]=[Table1.Record Number] then"Yes" else "No"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] = "No"),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table1.Record Number", "Table1.Starting Date", "Table1.Area", "Table1.Count", "Custom"})
in
    #"Removed Columns"


You can download the .pbix file for more details.

Best Regards,
Angelia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Thank you v-huizhn-msft

 

I believe I found a slightly simpler solution.

I took the original table (with sorting applied) and duplicated as you suggested.  In the new table I removed the duplicates.  Then I merged using Anti Join instead of Inner Join.

 

The Anti Join returns all rows from one table not found in the other table.  Saved a few steps with this method.