Forum Discussion
Identify rows with duplicate values
Look:
Here's the M code:
let Source = Invoices,
#"Merged Queries" = Table.NestedJoin(Source, {"Vendor", "Posting Date", "Amount"}, #"Invoices - Duplicate Invoice Marker", {"Vendor", "Posting Date", "Amount"}, "Invoices - Duplicate Invoice Marker", JoinKind.LeftOuter), #"Expanded Invoices - Duplicate Invoice Marker" = Table.ExpandTableColumn(#"Merged Queries", "Invoices - Duplicate Invoice Marker", {"Inv No"}, {"Inv No.1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Invoices - Duplicate Invoice Marker",{"Inv No.1"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Inv No", "Vendor", "Posting Date", "Amount"}, {{"Count", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Has Dups", each if [Count] > 1 then "Yes" else "No"), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Count"}) in #"Removed Columns2"Here are the table dependencies:
Best
Darek
My core issue is how to mark identical rows based on three criteria?
I understand why the If statement returns all positives, but thought it would help clarify my conundrum with the best example I could conjure up.
After hours of searching, I didn't come across any posted problems/solutions. I decided to describe my problem here as best possible, even though it is simple stuff for advanced users.
I get what you are saying mate, but when you hit that wall of knowledge, and google doesn’t do the trick, you need help, maybe even to formulate your problem..
Well, in PQ you've got something like Merge Queries. You could therefore merge the table with a copy of itself (it must be a copy, a reference will not work) and the merge would be LEFT OUTER JOIN on the three columns you want. For all rows in the left table you'd get matches (in the form of a Table stored in the cell - you have to expand it). Then what happens is that some rows in the left table will be duplicated at least 2 times and some will be left alone (where there's only one match - the row itself). Having this you can remove the column(s) from the right table and do a groupby on the rows that were left. The groupby should use the Count(*) function. If you do that each row will have a count of duplicates. Now you can add a column and say: if count > 1 then "yes" else "no".
Easy? Can you follow yourself or should I create a dummy file with all the steps inside and post a link to it? But remember that I'm at work, so it might be faster if you try to implement this yourself :)
Best
Darek