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
- AlexisOlson3 years ago
Super User
CNENFRNL Cool. I didn't know how to use that 4th argument of List.PostitionOf.
Personally, I prefer not to use positional functions (except for first/last sort of thing) given how often I make indexing errors, so I'd probably do it more like this:
let KeywordList = 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 = _t]), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYs7DoAgEAWv8kJtPIi1HaGAsAp+WLKg8fhup9UkM+9ZayZOBck3eAhFhLzTaNxgzZwIXYiQG1Zl+XTIEkG+66nW47df+MF2nbWBbxJ0VZFX7e4F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sentence = _t]), MatchList = Table.AddColumn(Source, "Matches", (r) => List.Select(KeywordList[Keyword], each Text.Contains(r[Sentence], _))), KeywordColumns = let MaxMatches = List.Max(List.Transform(MatchList[Matches], each List.Count(_))) in {"# keywords found"} & List.Transform({1..MaxMatches}, each "Keyword" & Number.ToText(_)), ToRecords = Table.TransformColumns(MatchList, {{"Matches", each let m = List.Count(_) in Record.FromList({m} & _, List.FirstN(KeywordColumns, m + 1)), type record}}), ExpandRecords = Table.ExpandRecordColumn(ToRecords, "Matches", KeywordColumns, KeywordColumns) in ExpandRecords - Chris13003 years ago
Helper II
Thanks 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,