Forum Discussion
Identify rows with duplicate values
I am new with Power BI and DAX, and hope the community can help with this specific issue.
The purpose is to identify potential duplicate invoices, which are registrered under a different invoice number (eg. Inv123 scanned again as Inv 123 or Inv.123).
As this is part of a larger analysis, I would prefer it could be as a Column, rather than using Power Query. The "Vendor Number", "Posting Date" and "Amount", would all be the same. Does anyone have an idea on how to mark these rows with a "Yes", and all others with a "No", or something similar?
Regards
Jonas
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
7 Replies
Thanks for taking your time reviewing my problem!
I don't mind using Power Query, but having the specific rows marked, while still retaining all the data, would be greatly preferred. I have a Data Model with many dimension tables and measures, so if at all possible, it would be benificial to just add a new column with "yes" and "no", where "yes" marks the rows which are suspected as duplicates.
I tried an If statement in a custom column in power query, but all results are positive:
if [Vendornumber] = [Vendornumber] and
[Amount] = [Amount] and
[Date] = [Date]
then "Yes"
else "No"How would you mark only the rows where alle three criteria are true?
Regards
Jonas
