Counting how many times a word is in a column

[bb72]

Hi, I have a column of text separated by a semi colon, e.g.

Col1

word1;word2

word1;word3

word3;

word4

word1

etc etc

I would like to count how many instances of each word in each row and have a total but comparing it against a table of possible words, result would be:

word 1 = 3

word 2 = 1

word 3 = 2

etc

I see an example here:

https://community.powerbi.com/t5/Desktop/Counting-how-many-times-a-word-is-in-a-column/m-p/206657#M9...

# Depression = CALCULATE(COUNTAX('TableName','TableName'[Emotional Depression]),'TableName'[Emotional Depression]="Depression")

But it is using a hard coded word to search for instead of a list in another table/column.

As I have 30 words to search for, would rather reference something else to supply statically in the search criteria.

Any suggestions pls?

thanks!

BB

[v-chuncz-msft]

@bb72,

You may first select Split Column By Delimiter in Query Editor.

[Greg_Deckler · Accepted solution]

Given the following Enter Data queries:

SearchWords

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs8vSjG0BpFGSrE6yHxjON/YGs40QShSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SearchWords = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SearchWords", type text}})
in
    #"Changed Type"

Words

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs8vSjFUitWBsIzgLGM4y0QpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Words = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Words", type text}})
in
    #"Changed Type"

Create this colum in your Words table:

Count = SUMX(SearchWords,IF(LEN(SUBSTITUTE(SearchWords[SearchWords],[Words],""))<LEN(SearchWords[SearchWords]),1,0))

[bb72]

Thank you, this is almost perfect. However my column of words I am searching for, is of format:

word1;word2

Which your suggestion doesn't match on unfortunately.

COUNTIF works nicely in Excel, is there an equivalent ?

Thank u so much!