Forum Discussion

hansei's avatar
hansei
Helper V
6 years ago

Distinct based on 2nd column

I'm having an issue trying to group the following requirement.

 

SourceFKNoteCreated
a1abc14-Mar
a1abc14-Mar
b1abc14-Mar
b1xyz14-Mar
c1abc14-Mar
c1klm14-Mar

 

I have data coming from various sources (.csv files), and records are not necessarily unique. So in the above table, the 1st and 2nd record are absolutely separate, even though they contain the same data because they were retrieved from the same source "a". The 3rd and 4th row are from last week's source "b" which may contain the same data as this week, and may contain data which was deleted.

 

Above, the 3rd row looks identical to either the 1st or the 2nd row - so a duplicate, and the 4th row is missing from the new source - so a deletion. The 5th row is also a duplicate of either the 1st or 2nd row, while the 6th is, again a unique record.

 

I have no reason to keep duplicate data, but do want to retain what appear to be duplicates from the same source and any new data. So, how would i keep the 1st, 2nd, 4th and 6th row?

Need Help

3 Replies

  • hansei's avatar
    hansei
    Helper V
    6 years ago

    Well, with that dearth of replies, I've decided to do the following

    • sort by source to keep most recent at top (and buffer)
    • remove duplicates
    • remove most recent source
    • combine with most recent source
    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      6 years ago

      Hi hansei ，

       

      First create an index column for later use to get the latest source：

      Next, create a column filter. Its value judgment logic is that when the value of Note is duplicated and the value of Source is different from the latest source, it returns 1; otherwise, it returns 0. Finally create a calculated table to filter the table with filter equal to 0:

       

       

       

      Table 2 =

VAR f =

    ADDCOLUMNS (

        'Table',

        "filter",

        VAR a = 'Table'[Index]

        VAR b =

            CALCULATETABLE (

                DISTINCT ( 'Table'[Note] ),

                FILTER ( 'Table', 'Table'[Index] < a )

            )

        VAR c =

            CALCULATE ( MAX ( 'Table'[Source] ), FILTER ( 'Table', 'Table'[Index] = 0 ) )

        RETURN

            IF ( 'Table'[Note] IN b && 'Table'[Source] <> c, 1, 0 )

    )

RETURN

    FILTER ( f, [filter] = 0 )

       

       

       

      Please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESq2wtkC4XFMhZcUOnGhS-IBWBfPlgm76jhF_hZCLi6oaA?e=6rSwH0

       

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

       

      Best Regards,

      Dedmon Dai

      • hansei's avatar
        hansei
        Helper V
        6 years ago

        I cannot have a static solution based on a,b,c. There may be hundreds of sources.