Forum Discussion
ukare1996
Helper I
Helper ICounting string words in semicolon separated column
Hi, I am trying to count all options selected separated by the semicolon. So for every semicolon marks an additional count. I am excluding all blanks, none and n/a columns. I am not sure how I can a...
ukare1996Helper I
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 |
ukare1996Helper I
Helper IThe count must exclude all None, Nulls and Blanks
- lbendlin
Super User
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
