Power Query Add Column w/ Lookup from a Different Table

[gimmeexcel]

I have a sheet now where I created a conditional column which required me to enter the data manually. I'd like to be able to this with Power Query grabbing the info from a separate table.
Example Code (Now):

#"XX Create Segments" = Table.AddColumn(#"Sorted Rows", "Segments", each if Text.Contains([Keyword], "service") then "services"
else if Text.Contains([Keyword], "small business") then "small business"
else if Text.Contains([Keyword], "compan") then "companies"
else if Text.Contains([Keyword], "custom") then "custom"
else if Text.Contains([Keyword], "agenc") then "agencies"
else if Text.Contains([Keyword], "professional") then "professional"
else if Text.Contains([Keyword], "firm") then "firms"
else if Text.Contains([Keyword], "commerce") then "e-commerce"
else if Text.Contains([Keyword], "package") then "packages"
else if Text.Contains([Keyword], "redesign") then "redesign"
else if Text.Contains([Keyword], "local") then "local"
else if Text.Contains([Keyword], "consult") then "consulting" else ""),
1. The reference for the PQ is a column named Keywords.
2. From the Keywords column, create new custom column based on the criteria. Name the custom column Segments
3. This is where I need help. I'd like a a way for PQ to get the criteria from a 2 column look table.
4. I'm not sure if there is a way to make it work for compound conditionals. For example if I wanted to include a word (maybe 2) but exclude another word. Perhaps I could set up a 3rd table for words I'd like to exclude?

All help would be greatly appreciated.

 

[mikewhitcombe]

Hello, did you ever solve this?