Forum Discussion
Chris1300
Helper II
3 years agoSearch for multiple keywords across table
I have 2 independent tables, not related in powerBI: Keyword List apple bird red tree John bike Sentence John has a red bike. The tree is green. The bird...
- 3 years ago
DAX is not created for addressing this although it's powerful enough to cover it.
For fun only, a showcase of powerful Excel worksheet formula,
CNENFRNL
Community Champion
3 years agolet
Keyword = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElVitWJVkrKLEoBM4pSIXRJUSpExis/Iw+qJBsoEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Keyword List" = _t]),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPISCxWSFQoSk1RSMrMTtVTitWJVgrJSFUoKUpNVcgsVkgH0nkI4aTMohSF1MQSoKaCghyQ+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sentence = _t]),
Matches = let kw = Keyword[Keyword List] in Table.AddColumn(Source, "match", each let pos = List.PositionOf(kw, [Sentence], 2, (x,y)=>Text.Contains(y,x)) in {List.Count(pos)} & List.Transform(pos, each kw{_}))
in
Matches
Chris1300
Helper II
3 years agoThanks for the solution in Power Query, but its really slow for my overall tables. Is this possible to do in DAX? I want to compare.
- CNENFRNL3 years ago
Community Champion
DAX is not created for addressing this although it's powerful enough to cover it.
For fun only, a showcase of powerful Excel worksheet formula,