Forum Discussion
Solved
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...
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..
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