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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

how to extract the count of a repeatable character in a text

Hello!

 

I have a column is a text, like aa,bb,aa,aa,aa,cc,aa,aa,.. I need to count how many times aa has appeared in this text. 
table is like this
ID       Action
001     aa,bb,aa,aa,aa,cc,aa,aa
002     bb,cc,aa,aa,aa,cc,aa,aa,aa,aa,aa

003     aa,dd,dd,aa,aa,aa

I need to add a column to show how many times aa appeared in action for each id. 

Thank you in advance for your kind help!

1 ACCEPTED SOLUTION
MDodds
Resolver II
Resolver II

Hi DuoHappy,

 

Try creating a new calculated column with the following DAX:

 

NumberofAs = LEN('Table'[Action])-LEN(SUBSTITUTE('Table'[Action],"a",""))
 
It should produce a result like so:
MDodds_0-1667990784214.png

 

Just be careful of case sensitivity.

 

Good luck, reach out if you need more help!

View solution in original post

4 REPLIES 4
Analystmate
Helper II
Helper II

@Anonymous

You can try a new custom column in the example below by using Power Query  

Count M Code for custom column  (Text.Length([#"Action"])-Text.Length(Text.Replace([#"Action"],"aa","")))/2

 

Analystmate_0-1667992903324.png

 

Anonymous
Not applicable

Hi @MDodds , Thank you very much! It is a very smart way! As I am looking for aa instead of a, I think the formula should be :  NumberofAAs = (LEN('Table'[Action])-LEN(SUBSTITUTE('Table'[Action],"aa","")))/2, right? 

Spot on, good result.

MDodds
Resolver II
Resolver II

Hi DuoHappy,

 

Try creating a new calculated column with the following DAX:

 

NumberofAs = LEN('Table'[Action])-LEN(SUBSTITUTE('Table'[Action],"a",""))
 
It should produce a result like so:
MDodds_0-1667990784214.png

 

Just be careful of case sensitivity.

 

Good luck, reach out if you need more help!

Helpful resources

Announcements
Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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

Top Solution Authors