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
Scheduled StartScheduled EndSite
3/11/2023 9:003/11/2023 17:00NSW 
8/11/2023 9:008/11/2023 17:00NSW 
10/11/2023 9:3010/11/2023 10:30NSW 
10/11/2023 10:3010/11/2023 11:30NSW 
10/11/2023 10:3010/11/2023 11:30NSW 
13/11/2023 13:0013/11/2023 16:00NSW 
13/11/2023 13:3013/11/2023 21:30NSW 
14/11/2023 9:3014/11/2023 17:30NSW 
16/11/2023 9:0016/11/2023 17:00NSW 
16/11/2023 12:0016/11/2023 20:00NSW 
17/11/2023 11:3017/11/2023 19:30NSW 
22/11/2023 11:3022/11/2023 11:30NSW 
22/11/2023 11:3022/11/2023 19:30NSW 
23/11/2023 9:3023/11/2023 17:30NSW 
24/11/2023 9:0024/11/2023 17:00NSW 
24/11/2023 9:0024/11/2023 17:00NSW 
24/11/2023 9:0024/11/2023 17:00NSW 
24/11/2023 9:0024/11/2023 17:00NSW 
27/11/2023 11:0027/11/2023 14:00NSW 
29/11/2023 9:3029/11/2023 17:30NSW 
29/11/2023 9:3029/11/2023 17:30NSW 
29/11/2023 9:3029/11/2023 17:30NSW 
29/11/2023 10:0029/11/2023 18:00NSW 

 

Many thanks

Anonymous's avatar
Anonymous
Not applicable
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.