Text.Contains for multiple values power query

Question

I am attempting to create the following query:

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

horseyride · Accepted Answer · 2022-07-15 23:35:14Z

3

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

edited Jul 15, 2022 at 23:35

answered Jul 15, 2022 at 23:18

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nick Over a year ago

Hey this works well however ive just come across a slight issue. If you convert the text to work for searching numbers, e.g 4.5. If the text contains 14.5 this is also pulled through. Do you know how this could be modified to match exactly?

Nick Over a year ago

For example, if you had: 15.2. Chemical safety assessment in table 2. and lookfor contained 5.2

horseyride Over a year ago

You could add quote space quote & in front of the [Column1] I suppose

Collectives™ on Stack Overflow

Text.Contains for multiple values power query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related