Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional deduplication based on multiple columns

Hi everyone,   I need to write a very specific deduplication query and I am wondering if you can help me out. We have an ecommerce site and I am getting a daily CSV file that has transactional info...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    Try this code.

    Remove Duplicate =
SUMMARIZE (
    FILTER (
        'Table',
        IF (
            VAR _COUNTROW =
                CALCULATE (
                    COUNTROWS ( 'Table' ),
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Master Order Number] = EARLIER ( 'Table'[Master Order Number] )
                            && 'Table'[Item ID] = EARLIER ( 'Table'[Item ID] )
                    )
                )
            RETURN
                _COUNTROW > 1,
            'Table'[RANK] <> 1
                || 'Table'[Invoice Date] <> BLANK (),
            'Table'[RANK] = 1
        )
    ),
    'Table'[Order Number],
    'Table'[Master Order Number],
    'Table'[Order Date],
    'Table'[Invoice Date],
    'Table'[Item ID]
)

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Super User
4 years ago

Deduplication is much easier to do in Power Query (unless it depends on user filters).

 

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

lbendlin thanks for the reply.

 

Table 1: Sample of data before filter showing all the order IDs within Master Order # 10034858.

 

Order NumberMaster Order NumberOrder DateInvoice DateItem ID
10034858100348587/12/2021 CA-ST-EA
10034858100348587/12/2021 CA-BB-CS
10034858100348587/12/2021 CC-USR-04
10034858100348587/12/2021 CC-UPTP-04
20405998100348587/12/2021 FP-20EHH-CS
20405999100348587/12/2021 3M-SGCUP-01
10034858100348587/12/20217/13/2021CC-USR-04
10034858100348587/12/20217/13/2021CC-UPTP-04
10034858100348587/12/20217/13/2021CA-ST-EA
10034858100348587/12/20217/13/2021CA-BB-CS
20405998100348587/12/20217/13/2021FP-20EHH-CS
20405999100348587/12/20217/13/20213M-SGCUP-01

 

Table 2: Data after filter applied; duplicate order IDs within Master Order # 10034858 WHERE Invoice Date = NULL have been removed.

(Remember I want this logic to look within all Master Order #s. I have just filtered down to one Master Order # to simplify the example output.)

 

Order NumberMaster Order NumberOrder DateInvoice DateItem ID
10034858100348587/12/20217/13/2021CC-USR-04
10034858100348587/12/20217/13/2021CC-UPTP-04
10034858100348587/12/20217/13/2021CA-ST-EA
10034858100348587/12/20217/13/2021CA-BB-CS
20405998100348587/12/20217/13/2021FP-20EHH-CS
20405999100348587/12/20217/13/20213M-SGCUP-01

 

Thanks again.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

    Try this rank code.

     

    RANK = 
RANKX (
    FILTER (
        'Table',
        'Table'[Order Number] = EARLIER ( 'Table'[Order Number] )
            && 'Table'[Master Order Number] = EARLIER ( 'Table'[Master Order Number] )
            && 'Table'[Item ID] = EARLIER ( 'Table'[Item ID] )
            && 'Table'[Order Date] = EARLIER ( 'Table'[Order Date] )
    ),
    'Table'[Invoice Date],
    ,
    ASC
)

     

    This code will create a rank based on each [Order Number],[Master Order Number],[Order Date] and [Item ID] group. 

    Then create a new dax table to remove rank =1.

     

    Remove Duplicate = 
SUMMARIZE(FILTER('Table','Table'[RANK]<>1),'Table'[Order Number],'Table'[Master Order Number],'Table'[Order Date],'Table'[Invoice Date],'Table'[Item ID])

     

    Result is as below.

    Or you can calcualte sum directly by add rank<>1 in your table filter. You don't need to create a new table.

    Such as:

    Measure = CALCULATE(SUM([Sales],Filter('Table',[Rank]<>1))

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous, thank you so much for the reply and for taking the time to help me out. I think this gets me closer but it's not quite what I need. I may not have explained it very clearly, but I only want to suppress rows when the following conditions are met:

       

      • There are duplicate Item IDs within a Master Order # AND those duplicate Item IDs contain a blank/NULL invoice date.

      So consider the screenshot below. The ones highlighted in green should be removed, and the white ones left alone, which your script did perfectly.

       

      But the yellow ones I highlighted, which are part of a different Master Order #, should not be removed because they do not have duplicate Item IDs nor do they have a blank Invoice Date. So I don't want to remove the yellow rows. Does this help clarify what I am asking for?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

        Add a filter in calculated table code.

        Remove Duplicate =
SUMMARIZE (
    FILTER ( 'Table', OR ( 'Table'[RANK] <> 1, 'Table'[Invoice Date] <> BLANK () ) ),
    'Table'[Order Number],
    'Table'[Master Order Number],
    'Table'[Order Date],
    'Table'[Invoice Date],
    'Table'[Item ID]
)

        Result is as below.

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.