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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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