Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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
The solution is simple 🙂
Text.Length([ColumnName]) - Text.Length(Text.Replace([ColumnName], "-", ""))
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.
User | Count |
---|---|
122 | |
69 | |
67 | |
58 | |
52 |
User | Count |
---|---|
187 | |
94 | |
67 | |
63 | |
54 |