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.
Hi Digalo,
You can use the Merge tables technique shown in session 3.04 to bring the 'segment' data into your data table from a lookup table.
e.g. your lookup table will contain two columns, the phrases from the 'Keyword' column and the corresponding 'Segment'.
You then merge your data table with your lookup table and use the 'Keyword' column as your match.
Mynda
I don't think merge will work because it's really a search function. Searching for a partial match with the text in a different column. It's not an exact match
like merge.
Ok, perhaps you can upload a sample Excel file so we can get a feel for the data you're working with and offer a suitable solution.
Mynda
Thanks. I'm adding a sample file.
Basically, I'm try to add some tags to the (Keywords) table via a conditional column. The problem is inputting the data manually.
I'd like to incorporate a second table (Lookup) to put the parameters in for the conditional column.
Hi Digalo,
Thanks for the sample file. In the attached Excel file you'll see a query with your segments.
Note: some keywords contain multiple 'Find' values e.g. Atlanta ecommerce web design company contains both 'atlanta' and 'commerce' and so you have two records returned, one in each segment. Obviously this is an example file and your proper data won't contain duplicates, but please be aware.
Mynda
Thanks so much, Mynda. This helps me out a LOT. I really appreciate your time.
