Forum Discussion

collis's avatar
collis
Frequent Visitor
1 year ago
Solved

Count duplicates (checking multiple columns) and displaying count in new column

Hello all I am working with data with duplicates; however, I can't remove them as I need to use other columns in other charts. I would ideally like to create the below - where the first three colum...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, collis 

     

    You can try the following methods. Start by adding the index column to the Power query.

    Count =
CALCULATE ( COUNT ( 'Table'[Site] ),
    FILTER (
        ALLEXCEPT ('Table','Table'[Scheduled Start],'Table'[Scheduled End],'Table'[Site]),
        [Index] <= EARLIER ( 'Table'[Index] )
    )
)

    Is this the result you were expecting?

     

    Best Regards,

    Community Support Team _Charlotte

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

collis's avatar
collis
Frequent Visitor
1 year ago

Thank you. So fast. This works well, but it deletes other columns. How would I keep other columns to be used for other charts?

collis's avatar
collis
Frequent Visitor
1 year ago

Thank you again for the reply. Unfortunately, my knowledge isn't advanced enough.  Trying though... If I list all the columns, the count won't work as the data in the other columns isn't duplicated. I only need to check duplicates across three columns, and then the other columns I need to keep to be able to do other charts. I have seen people add 

{"all", each _, type table [membercode=number, capacity=number]}

but I can't get it to work.  Sorry, this is really stretching my brain!