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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors