Forum Discussion
Helper IISearch 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 eats apple. |
I would like to create calculated columns next to "Sentence" table, with following columns: # of Keywords found, Keyword1, Keyword2, Keyword3, Keyword4. So the final table looks like this:
| Sentence | # keywords found | Keyword1 | Keyword2 | Keyword3 | Keyword4 |
| John has a red bike. | 3 | John | red | bike | |
| The tree is green. | 1 | tree | |||
| The bird eats apple. | 2 | bird | apple |
I only need to find 4 keywords. What formula can I use in DAX or power query to create these additional columns? Which one would be fastest, I do have many many rows. I think power query would be.
Thanks!
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,
7 Replies
- Ashish_Mathur
Super User
Chris1300Thanks 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.
CNENFRNLCommunity Champion
let 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- AlexisOlson
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 - Chris1300
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.
- CNENFRNL
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,
