cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

@Justair07 

 

if you want the text to not contain something you can just add a NOT:

 

Test = Table.AddColumn(ChangedType, "Non-Conforming", each List.Count(List.Select(Record.ToList(_), each not Text.Contains(Text.From(_), <> "Conforming"))), 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 ok that is very helpful. Thank you. Is the AND operator an option in M as well? Instead of using NOT I can just add more conditions for various words. I hope this make s sense.

Highlighted
Super User
Super User

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

Hi @Justair07 

 

you could add more words like this

 

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") or Text.Contains(_, "Yellow")
                        )
                     ), Int64.Type)
in
    Test

 


 


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


Proud to be a Datanaut!