Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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 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.
@smpa01 ,
Thank you so much! It took a little bit of messing around with my actual data but it worked.
Thank you, again.