• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerQuery lookup&replace based on keywords from another table

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
 

Attachments

  • PowerQuery lookup&replace based on keywords from another table.xlsx
    314.5 KB · Views: 2
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.
Code:
=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.
 

Attachments

  • PowerQuery lookup&replace based on keywords from another table.xlsx
    324 KB · Views: 5
Back
Top