PowerQuery lookup&replace based on keywords from another table

[Lars Ole Sørensen]

Hi

I am wondering if it is possible via the power Query editor to do the following. In a new column (Fish species) extract keyword from the column "beskrivelse" based on if it contains a keyword that is included in the table "FishSpecies_Table". I have done this for one keyword "Torsk" with the formula "Text.Contains", but this is with hard coded text. Is there a way to look up the keyword in the column "beskrivelse" and if a match put the keyword in the column "Fish Species".

I will be very greateful on any help on this problem

I have attached the workbook with my temporary query.

Kindly Regards
Lars Ole

 

[Chihiro]

Here's one method. It's probably the simplest and does not require creating custom M function.

1. Load "FishSpecies_Table" into PQ editor.
2. Add custom to MaritechData_Table with formula =FishSpecies_Table, and expand the column for Fish Species. This will create cross join table with every possible combination.
3. Change data type for Fish Species to Text.
4. Add custom column with following formula.

=if Text.Contains([beskrivelse],[Fish Species]) then [Fish Species] else null

5. Filter out nulls in Custom Column, and remove the column.

Done.

See attached.

 

[Lars Ole Sørensen]

Thank you very much Chihiro. This is exactly what I'm looking for.

Regards
Lars Ole