Forum Discussion

BLB's avatar
BLB
Icon for Helper I rankHelper I
2 years ago

Identifying and listing duplicates across multiple columns

I am looking for a solution either in Power Query or in DAX to be able to identify customer orders which are placed by error more than once.

 

A customer order (Order ID) is considered 'duplicate' if there is another Order ID with the:

- same Order Date +

- same Customer Reference +

- same List of Materials +

- same Order quantities for each material.

 

It is important that one order usually consist of multiple Material codes and we want to find 'duplicate' orders only if the whole list of material and order quantities are the same. 

 

 

Any help appreciated.

 

Dax Help
Duplicate data

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi BLB 
    Please try

    Duplicate Orders =
CALCULATETABLE (
    VALUES ( Orders[Order ID] ),
    FILTER (
        SUMMARIZE (
            Orders,
            Orders[Order Date],
            Orders[Customer Reference],
            Orders[List of Materials],
            Orders[Order Qty]
        ),
        COUNTROWS ( CALCULATETABLE ( Orders ) ) > 1
    )
)