Counting occurrences of a string value

[UBComma]

I am trying to create a custom column and having difficulty with the M code for the custom column. I have two tables, one contains a list of projects and has names for each project. The second table has keywords and I want to count how many times the value in the keyword column is contained in the text string of the 'Name' column. I would really appreciate any help

Project Titles

Index	Name
1	Fix broken pipe in building 3
2	Replace roof on main building
3	Generator roof leaking
4	HVAC failed in building 10
5	HVAC failed in building 35
6	Lower roof blew off in wind storm

Keywords

Keyword	Count <<<Custom Column I'm trying to create
pipe	1
roof	3
HVAC	2

[adudani · Accepted solution]

hi @UBComma

create two blank queries and copy paste the following in the advanced editor:

project titles:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc0xDgIhEEDRq0yot3DFtTcmamFlYUMoQAYzWZYhqMHji6iJNtb/JV8p0YtObOgONvOIERIlBIpgbxQcxTNIoTsl5lUdMAVzQsjMHjjCZL5cU7KqLUbM5sr55QKa8ZMXNe+OqzV4QwHdz6afNTL8IXJoZFnJngu+BzZgAfb+SQtFB5f6noTWDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Name", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Name", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Name", Text.Clean, type text}})
in
    #"Cleaned Text"

Keywords

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKsgsSFWK1YlWKsrPTwMzPMIcnZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Keyword", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Keyword", Text.Upper, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Keyword", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Keyword", Text.Clean, type text}}),
    Result = Table.AddColumn(#"Cleaned Text", "Result", each List.Count(List.FindText(#"Project Titles"[Name],[Keyword])))
in
    Result

Output

steps taken:

1. used clean trim and upper on the text strings in both queries.

2. added a custom column in keywords to bring the names from project tiles as a list.

3. used list.findtext against the key words

4. used list.count to get the result.

instead of using the trim/clean/upper you could also use ignore case function if appliable for a shorter query.

Appreciate a thumbs up if this is helpful.

let me know if this resolves the question

[UBComma]

Brilliant, that worked really well, thank you so much fo your help!