cancel
Showing results for
Did you mean:
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.

1 ACCEPTED SOLUTION
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:

Just be careful of case sensitivity.

Good luck, reach out if you need more help!

4 REPLIES 4
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

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?

Resolver II

Spot on, good result.

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:

Just be careful of case sensitivity.

Good luck, reach out if you need more help!

Announcements

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.

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

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!

2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors