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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

5 REPLIES 5
tsibilski
Advocate I
Advocate I

The solution is simple 🙂
Text.Length([ColumnName]) - Text.Length(Text.Replace([ColumnName], "-", ""))

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 2025 and this post help me.

It's 2022, thank you @d_gosbell 

it's 2020 but thid post really helped me.

Thank you @d_gosbell !!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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