Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago
Solved

Count repeated data in column

Hi, I need to count the amount of repeated data from the [Concatenation] column

Of that, I need to count which of them are repeated, and if they are what number it represents.


Example:


ASD
ASD
ASD
ASD
azx
azx
AJH

In this case asd is repeated 4 times, but the formula I manage to obtain gives me as a result 4.

ASD = 4
ASD = 4
ASD = 4
ASD = 4

And I want it to appear as follows:

ASD = 1
ASD = 2
ASD = 3
ASD = 4


For the previous example it would therefore be:



ASD =1
asd=2
asd=3
asd=4
azx=1
azx=2
ajh=1


That should be as a column with formula and not as a calculated measure since what I need is to consider only those data that result in the number 1 and the rest I do not have to consider.


Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    This M code works

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.ExpandTableColumn(Table.Group(Source, {"Text"}, {{"All", each Table.AddIndexColumn(_,"Count",1), type table}}), "All", {"Count"})
in
    #"Grouped Rows"

     

     

    Hope this helps.

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This M code works

     

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.ExpandTableColumn(Table.Group(Source, {"Text"}, {{"All", each Table.AddIndexColumn(_,"Count",1), type table}}), "All", {"Count"})
in
    #"Grouped Rows"

     

     

    Hope this helps.

  • DataVitalizer's avatar
    DataVitalizer
    Super User
    3 years ago

    Hi
    1. Add an index column to the table using Powerquery

    2. Add a calculated column using this DAX code

     New Column= CALCULATE( COUNTROWS('Table'), FILTER( 'Table', EARLIER( 'Table'[Concatenation]) = 'Table'[Concatenation]  && 'Table'[Index] <= EARLIER( 'Table'[Index]) ))


    Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

    • DataVitalizer's avatar
      DataVitalizer
      Super User
      3 years ago

      Could you share a screenshot of yout table so I can guide you?