Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cmaloyb
Helper II
Helper II

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

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!

 

 

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

2 REPLIES 2
smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@smpa01 ,

 

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

 

Thank you, again.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors