Forum Discussion
Helper ICounting string words in semicolon separated column
Super UserPlease provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Helper IPlease find attached below - how can I count e.g. the strings in column 1 to return a result of 10 - so count all the strings - each time an occurance of semicolumn signifies an additional count. How do I do this without creating a seperate table and spliting then unpivoting - as I have more these columns in different categories. Any help would be much apprecaited.
Monitor 1 - Action taken by customer40.Monitor 2 - Action taken by customer Monitor 3 - Action taken by customer
| Private drains cleaned;#GM UPGRADED following NPO visit/advice | N/A | GM UPGRADED following NPO visit/advice |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| GM installed following NPO visit/advice | None | |
| None | None | None |
| None | None | None |
| None;#GM installed following NPO visit/advice;#GM UPGRADED following NPO visit/advice;#Private drains cleaned | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| None | None | None |
| Improved kitchen practices | GM purchased but not yet fitted | GM purchased but not yet fitted |
| None | None | None |
| None | None | None |
| Private drains cleaned;#GM - Increased frequency of cleaning | GM UPGRADED following NPO visit/advice;#GM (ADDITIONAL) installed following NPO visit/advice | Improved kitchen practices;#GM - Increased frequency of cleaning |
| GM installed following NPO visit/advice | GM installed following NPO visit/advice | GM installed following NPO visit/advice |
| None | None | None |
- ukare1996
The count must exclude all None, Nulls and Blanks
- lbendlin
Something like this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVO9CsIwEH6Voy4Kig/gVKhIQWsRnUqHmF41GJOaXit9e0NcHKxGdHBJ4PiSu+/nsixIjWgZIRSGCVUDl8gUFrPBYgW7dLEJo3kEpZZSX4U6QJKuoRW1oCkrWsExGAfJNLSnJzwfZ0GilXv3cP1X2XKxUhCTEovX3OG73k5ln1a+dswGz+38D117yvG5Mrq1/E+C+BEVVIZxsmzqe66qxvAjqy1g3xAoTdAhQSmIHLF3iM8HerESE4gVN+h6lQYvDSregS7vIGuJ9yq474ZhFMXbeJ2Ey5Fv5vrl8pzws4j/HtmnfH4D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), Col1 = List.Select( Text.Split(Text.Combine(Source[Column1],";"),";"), each _ <> "None") in Col1