If Text Contains Value from Another Table, Return All that are True

[cmaloyb]

Hi,

Key Word Table

[Common Word]

wrench

tighten

screwdriver

bolt

Solutions Table

[CommonSolutions]

Use a wrench to tighten the bolt.

Use a screwdriver to loosen.

I have two tables I am working with - Key Word table and Solutions Table. In the Solutions table, I would like to add a column called 'SolutionKeyword' that displays a Key Word[Common Word] found in the Solutions[CommonSolutions] field. If more than one [Common Word] appears in [CommonSolutions], I would like for the Solutions Table record to duplicate for however many more [Common Words] there are in [CommonSolutions] and display each of the different [Common Words] on each row. 

Here is what I'd like for my Solutions Table to look like. 

[CommonSolutions]	[Common Word Found]
Use a wrench to tighten the bolt.	wrench
Use a wrench to tighten the bolt.	tighten
Use a wrench to tighten the bolt.	bolt
Use a screwdriver to loosen.	screwdriver

I have used the solution found at this link: https://community.powerbi.com/t5/Power-Query/If-text-contains-value-from-list-then-return-that-value... , however this will only return the first [Common Word] found. These were the Query steps I used:

let
Source = #"Solutions",
#"Added Custom1" = Table.AddColumn(Source, "CheckForKeyword", each List.Transform( #"Key Word"[Common Word] , (x) => Text.Contains([CommonSolutions], x) )),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Contains_Keyword", each List.AnyTrue([CheckForKeyword]) ),
#"Added Custom" = Table.AddColumn(#"Added Custom2", "Keyword_Found", each try #"Key Word" {List.PositionOf([CheckForKeyword], true)} [Common Word] otherwise null),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CheckForKeyword", "Contains_Keyword"})
in
#"Removed Columns"

Thanks!

[smpa01 · Accepted solution]

@cmaloyb  you can try it in this way

let
    src=let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/If-Text-Contains-Value-from-Another-Table-Return-All-that-are/m-p/2171350#M64145"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(4) > * > TR > :nth-child(1)"}}, [RowSelector="TABLE:nth-child(4) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"[Common Word]", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"[Common Word]", "Word"}})
in
    #"Renamed Columns",
    Target = let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Power-Query/If-Text-Contains-Value-from-Another-Table-Return-All-that-are/m-p/2171350#M64145"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(7) > * > TR > :nth-child(1)"}}, [RowSelector="TABLE:nth-child(7) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"[CommonSolutions]", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"[CommonSolutions]", "Transaction"}})
in
    #"Renamed Columns",
    #"Added Custom" = Table.AddColumn(Target, "Custom", each let
j=[Transaction],
Y=src[Word],
Loop = List.Generate(
()=>[a=0,b=Y{a},c=if Text.Contains(j,b)=true then b else null],
each [a]<List.Count(Y),
each[a=[a]+1,b=Y{a},c=if Text.Contains(j,b)=true then b else null],
each [c])
in
List.RemoveNulls(Loop)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

pbix is attached.

[cmaloyb]

@smpa01 ,

Thank you so much! It took a little bit of messing around with my actual data but it worked. 

Thank you, again.