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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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