Search for multiple keywords across table

[Chris1300]

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!

[CNENFRNL]

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

[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 · Accepted solution]

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,

[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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[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.

[AbbasG]

@Chris1300 I think you can achieve this by splitting the column by space as below. 

Steps:
1. Go to power query
2. Duplicate the sentence column
3. Split a column by a custom delimiter (Space)
4. Rename the column

I hope it helps you to achieve the desired output.