excelpowerquerytext-search

Text.Contains for multiple values power query

I am attempting to create the following query:

enter image description here

The idea is to check if each row in the source query contains any of the following keywords in the Search list and return the Found words is present.

Importantly I need this to be dynamic i.e. the search list could be a single word or could be 100+ words. Therefore I need to work around just stitching a bunch of Text. Contains with or statements is possible.

In effect, I want to create something like

Text.Contains([Column1], {any value in search list}) then FoundWord else null

Data:

Physical hazards Flam. Liq. 3 - H226 Eliminate all sources of ignition. 
Health hazards STOT SE 3 - H336.  Avoid inhalation of vapours and contact with skin and eyes.
Environmental hazards Not Classified. Avoid the spillage or runoff entering drains, sewers or watercourses.
Personal precautions Keep unnecessary and unprotected personnel away from the spillage.
clothing as described in Section 8 of this safety data sheet. Provide adequate ventilation.

Search List:

Hazards
Eliminate
ventilation
Avoid
Solution

  • try this code for query Table2 after creating query lookfor

    let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Findmatch = Table.AddColumn(Source, "Found",  (x) => Text.Combine(Table.SelectRows(lookfor, each Text.Contains(x[Column1],[Column1], Comparer.OrdinalIgnoreCase))[Column1],", "))
in Findmatch

    enter image description here