cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to count a specify letter in a string in power query m?

Hi all,

 

I would like to count a specific character (or letter) in a string in power query:

 

For example:

I have a column call Functional Location and contains a string value with one or more than one hypen ("-")

I would like to count the number of hypen in each of the row string.

Hence, if "AU-GOV-DEFN-DBS-EW-PEA-ARRF". the correct result will give me 6

if "AU-GOV", the correct result will give me 1.

 

I try the following by creating a custom column but I get error:

* Text.PositionOfAny([Functional Location], "-", Occurrence.All)

 

Even if the above function is correct, it will not give me the whole number because it will give me a list of the position of hypen.. as there is no Count function in Text.

 

F1.JPG

 

Appreciate your help.

 

Regards,

Tuff

 

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

Test.PositionOfAny expects a list of characters as the second parameter. But because you are just looking for a single character you could just use Text.PositionOf then count the list of returned positions

 

List.Count(Text.PositionOf([Functional Location],"-",Occurrence.All ))

 

Or you could turn the "-" parameter into a list by enclosing it in curly braces

eg.

 

List.Count(Text.PositionOfAny([Functional Location],{"-"},Occurrence.All ))

 

Note, clicking on the Error will show you a more detailed error message 

View solution in original post

3 REPLIES 3
d_gosbell
Super User
Super User

Test.PositionOfAny expects a list of characters as the second parameter. But because you are just looking for a single character you could just use Text.PositionOf then count the list of returned positions

 

List.Count(Text.PositionOf([Functional Location],"-",Occurrence.All ))

 

Or you could turn the "-" parameter into a list by enclosing it in curly braces

eg.

 

List.Count(Text.PositionOfAny([Functional Location],{"-"},Occurrence.All ))

 

Note, clicking on the Error will show you a more detailed error message 

It's 2022, thank you @d_gosbell 

it's 2020 but thid post really helped me.

Thank you @d_gosbell !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors