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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
1 year ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Scheduled Start", "Scheduled End", "Site"}, {{"Count", each Table.AddIndexColumn(_,"Index",1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Index"}, {"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Count",{{"Scheduled Start", type datetime}, {"Scheduled End", type datetime}, {"Site", type text}, {"Index", Int64.Type}})
in
    #"Changed Type"

Hope this helps.

 

  • 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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      1 year ago

      You are welcome.  In the Grouped Rows step, mention all the columns.

    • 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!