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

[Anonymous]

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.

Appreciate your help.

Regards,

Tuff

[tsibilski]

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

[d_gosbell · Accepted solution]

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 

[suebayes]

It's 2022, thank you @d_gosbell 

[mussaenda]

it's 2020 but thid post really helped me.

Thank you @d_gosbell !!