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

4 REPLIES 4
tsibilski
Frequent Visitor

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 2022, thank you @d_gosbell 

it's 2020 but thid post really helped me.

Thank you @d_gosbell !!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors