- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Helpful resources
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.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
8 | |
7 | |
6 |