cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DuoHappy
Helper III
Helper III

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 I
Helper I

@DuoHappy

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

 

DuoHappy
Helper III
Helper III

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.