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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.