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.

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!