Count repeated data in column

[Syndicate_Admin]

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 · Accepted solution]

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.

[Ahmedx]

pls see my video

Inbound Count repeated data in column.mp4

[Syndicate_Admin]

It doesn't work for me. Is there a video to see what the procedure is like?

[DataVitalizer]

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

[DataVitalizer]

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