Reply
UBComma
Helper III
Helper III
Partially syndicated - Outbound

Counting occurrences of a string value

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

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

 

Keywords

KeywordCount <<<Custom Column I'm trying to create
pipe1
roof3
HVAC2
1 ACCEPTED SOLUTION
adudani
Super User
Super User

Syndicated - Outbound

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

 

adudani_0-1675559326923.png

 

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

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

View solution in original post

2 REPLIES 2
adudani
Super User
Super User

Syndicated - Outbound

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

 

adudani_0-1675559326923.png

 

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

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Syndicated - Outbound

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

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)