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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors