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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.