cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Justair07 Member
Member

Count Number of Times a Word is Found in All Columns of a Table

Hello,

 

Does anyone know of a way in DAX or maybe a different solution to check all columns in a table for a certain word and if the word is found return a 1 or even a count of how mant times the work was found?

 

For example, I need the count of Red for a record but want to check all columns without listing them in my DAX

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count Number of Times a Word is Found in All Columns of a Table

Hello @Justair07 

 

you can do this easily with power query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRcsopTQVSEA6IjI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),

    Test = Table.AddColumn(ChangedType, "WordCount", each List.Count(List.Select(Record.ToList(_), each Text.Contains(_, "Red"))), Int64.Type)
in
    Test

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

12 REPLIES 12
Super User
Super User

Re: Count Number of Times a Word is Found in All Columns of a Table

Hello @Justair07 

 

you can do this easily with power query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkpNUdJRcsopTQVSEA6IjI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),

    Test = Table.AddColumn(ChangedType, "WordCount", each List.Count(List.Select(Record.ToList(_), each Text.Contains(_, "Red"))), Int64.Type)
in
    Test

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Justair07 Member
Member

Re: Count Number of Times a Word is Found in All Columns of a Table

@LivioLanzo  thank you. Is this the only solution you know of which requires the nameing of the columns? If I add a column to the table, will I also have to add it to the query? Is there a more dynamic solution that you know of?

Super User
Super User

Re: Count Number of Times a Word is Found in All Columns of a Table

Hi @Justair07 

 

the last line of the query (which is the one of the calculation) is fully dynamic and will work for all the columns

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Justair07 Member
Member

Re: Count Number of Times a Word is Found in All Columns of a Table

Do all columns in the table have to be Text? What if I only want to check the already existing text columns? I'm getting an error:

error.JPG

Super User
Super User

Re: Count Number of Times a Word is Found in All Columns of a Table

You can convert them all first to Text like this

 

 

Test = Table.AddColumn(ChangedType, "WordCount", each List.Count(List.Select(Record.ToList(_), each Text.Contains(Text.From(_), "Red"))), Int64.Type)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Justair07 Member
Member

Re: Count Number of Times a Word is Found in All Columns of a Table

@LivioLanzo but what if I need the date fields to remain date fields, number fields to remain number fields, etc..?

Super User
Super User

Re: Count Number of Times a Word is Found in All Columns of a Table

this will not change the data type of the original column

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
Justair07 Member
Member

Re: Count Number of Times a Word is Found in All Columns of a Table

Perfect! Thank you!

Justair07 Member
Member

Re: Count Number of Times a Word is Found in All Columns of a Table

@LivioLanzo  is it possible to edit this line to count all text that IS NOT "Conforming"

 

Test = Table.AddColumn(ChangedType, "Non-Conforming", each List.Count(List.Select(Record.ToList(_), each Text.Contains(Text.From(_), 
"Non-Conforming"))), Int64.Type)

something like this

Test = Table.AddColumn(ChangedType, "Non-Conforming", each List.Count(List.Select(Record.ToList(_), each Text.Contains(Text.From(_),
<> "Conforming"))), Int64.Type)

or maybe add an OR operator

Test = Table.AddColumn(ChangedType, "Non-Conforming", each List.Count(List.Select(Record.ToList(_), each Text.Contains(Text.From(_), 
OR("Non-Conforming", "Failed")))), Int64.Type)