Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
UBComma
Helper III
Helper III

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

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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors