Forum Discussion

Chris1300's avatar
Chris1300
Icon for Helper II rankHelper II
4 years ago
Solved

Search 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...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 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's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago
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's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

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