Counting string words in semicolon separated column

[ukare1996]

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 achieve this.

Any help would be much appreciated.

[lbendlin]

Please 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-...
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

[ukare1996]

Please 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