Counting occurrence of a particular text in a row

[PHEstaciMa1]

Hi Everyone, I wanted to count occurrence of a particular text in a row filled with multiple entry data e.g. risk prediction, SOP, caution, compliance in a column. Is there a way how to do it? 

[v-xiaotang · Accepted solution]

Hi @PHEstaciMa1 

Is the result similar to what you want?

If yes, you can try this,

1. create a value list table,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY4xCoAwDEWvEjp7CcFVEB0cikOwoQQ1labg9dW6iKjr433et9b0IU7rjCNBRcpezFBY07JO0ERyPCYOFztFFg9dQnEYnWZabhhJSBVqFPS0kKQC8r5UPfgF3sePzJf207h/HHY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work systems" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Work systems", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Work systems.1", "Work systems.2", "Work systems.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Work systems.1", type text}, {"Work systems.2", type text}, {"Work systems.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

2. count each value 

count = CALCULATE(COUNTROWS('Table'),FILTER('Table',CONTAINSSTRING('Table'[Work systems],MAX(test[Value]))))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[PHEstaciMa1]

Hi Team_Tang, I manage to replicate what you have advise, small glitch is that, whenever I split the column and unpivot it again, there are some data that it is being repeated (even though, I remove the duplicates already). The initial problem I see is that "spaces" between the words make it unique. How can I cleanse this? 

[v-xiaotang]

Hi @PHEstaciMa1 

Thanks for your reply!

You can right click on the column, and select Remove Duplicates

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[PHEstaciMa1]

Hi Team_Tang, I figured it out, I used "trim" to remove the spaces before the text and "remove duplicates" its now well working... Again, thank you for the advise! I'll accept it as a solution.

[amitchandak]

@PHEstaciMa1 , You have search each

Countrows(filter(Table,CONTAINSSTRING([Columns],"risk prediction") || CONTAINSSTRING([Columns],"SOP") ))

Add others with and (&&)  / or(||)

[PHEstaciMa1]

Hi @amitchandak just to further clarify, will this become a measure? OR do it will generate a separate column? thanks!

[amitchandak]

@PHEstaciMa1 , New measure. You can add more filters, if need. But if you want multi select in slicer , that would be little difficult