Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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!
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!
@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?
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!
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:
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!
@LivioLanzo but what if I need the date fields to remain date fields, number fields to remain number fields, etc..?
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!
Perfect! Thank you!
@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)
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!
@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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |