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

Question

Hi,&nbsp;Key Word Table[Common Word]wrenchtightenscrewdriverbolt&nbsp;Solutions Table[CommonSolutions]Use a wrench to tighten the bolt.Use a screwdriver to loosen.&nbsp;&nbsp;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.&nbsp;&nbsp;Here is what I'd like for my Solutions Table to look like.&nbsp;[CommonSolutions][Common Word Found]Use a wrench to tighten the bolt.wrenchUse a wrench to tighten the bolt.tightenUse a wrench to tighten the bolt.boltUse a screwdriver to loosen.&nbsp;screwdriver&nbsp;I have used the solution found at this link:&nbsp;https://community.powerbi.com/t5/Power-Query/If-text-contains-value-from-list-then-return-that-value/td-p/958481&nbsp;, however this will only return the first [Common Word] found. These were the Query steps I used:&nbsp;letSource = #"Solutions",#"Added Custom1" = Table.AddColumn(Source, "CheckForKeyword", each List.Transform( #"Key Word"[Common Word] , (x) =&gt; 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"&nbsp;Thanks!&nbsp;&nbsp;

smpa01 · Accepted Answer

cmaloyb&nbsp; 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) &gt; * &gt; TR &gt; :nth-child(1)"}}, [RowSelector="TABLE:nth-child(4) &gt; * &gt; 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) &gt; * &gt; TR &gt; :nth-child(1)"}}, [RowSelector="TABLE:nth-child(7) &gt; * &gt; 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(
()=&gt;[a=0,b=Y{a},c=if Text.Contains(j,b)=true then b else null],
each [a]&lt;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"
&nbsp;
pbix is attached.

Forum Discussion

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

2 Replies

Related content

check if value contains in another table

Exporting to PDF doesn't display the images contained in a table.

If column contains values from column in another table...

Table not contains

Contain Word

[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