Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Syndicate_Admin
Administrator
Administrator

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.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ahmedx
Super User
Super User

Syndicate_Admin
Administrator
Administrator

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

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

DataVitalizer
Solution Sage
Solution Sage

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors