February 23, 2017
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.
July 16, 2010
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
July 16, 2010
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
1 Guest(s)