Forum Discussion

JonasWesth's avatar
JonasWesth
Frequent Visitor
7 years ago
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...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

JonasWesth's avatar
JonasWesth
Frequent Visitor
7 years ago

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..

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • JonasWesth's avatar
    JonasWesth
    Frequent Visitor
    7 years ago

    Hi Darek

    Your solutions is elegant and brilliant. The final table clearly shows which have duplicates and are candidates as same invoices scanned twice!

    Thanks for the detailed description, including conjuring up your own example dataset! It was easy to follow, and allowed me a huge leap in knowledge of fundamentals of PQ and M!

     

    Much appreciated

    Jonas