• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Create Conditional Column Using Lookup Table|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Create Conditional Column Using Lookup Table|Power Query|Excel Forum|My Online Training Hub

office scripts course

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCreate Conditional Column Using Loo…
sp_PrintTopic sp_TopicIcon
Create Conditional Column Using Lookup Table
Avatar
Tony Newnham
Member
Members
Level 0
Forum Posts: 8
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
1
July 20, 2021 - 12:29 am
sp_Permalink sp_Print sp_EditHistory

Hi Mynda,

Refer to Conditional Column w/ Lookup from a 2nd Table

I'm looking to do the same thing as Digalo asked above so hope this is how I re open this tread?

Is there a method to do this with a much larger data set in the "lookup" table in your example file.

I can spend the time to build out a massive step using M for an add conditional column but I presume there is a way as i want to re use the look up table on a near daily basis for each time an get a new data set for processing.

Cheers Tony

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 174
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
2
July 20, 2021 - 1:51 am
sp_Permalink sp_Print

I would do this with a parameter query as shown in the attached.  Create a new table of key words.  Insert a column and filter for the key word with an if Text.Contains  statement.

Avatar
Tony Newnham
Member
Members
Level 0
Forum Posts: 8
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
3
July 20, 2021 - 7:52 pm
sp_Permalink sp_Print

Thanks Alan,

I appreciate the input but can't see what the parameter key achieves and appears to my PQ newbie eye as just a third table so I might elaborate a bit more as my explanation was not great.

I have a data set with items that are assigned by brand (Make Column) and depending on the model details (Model Column) they are also assigned into a Category (Category Column) and then a sub category (Sub Category Column) and these 4 columns are part of 20 columns needed for each item in the data set. I have extracted sample data and build out a reference list of brands to use in a conditional look up for the Make and it is over 1,000 lines and likley to keep growing slowly.

We have around 20 categories and they have 350 sub categories. The new data I am collecting uses similar but not matching sub categories to ours so I have mapped these and created a 3 column table that I also want to use in a conditional look up to find their sub cat in a text string and then assign ours in the new column.

While both of these tasks can be done building out a super large conditional look up I'm assuming PQ will allow me to refer to these data sets so they can stay as tables and be updated and so I can also use them on other builds.

Digalo's example has his look up table in the same sheet as his source data, but I can't work out how set up my query to connect to my reference tables that are not in the same sheet or in the same work book.

Hopefully someone might have done this or similar before.

Sorry if that is confusing.

 

Thanks again for any feedback.

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 174
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
4
July 21, 2021 - 12:15 am
sp_Permalink sp_Print

Suggest you upload a sample of your data that is representative of your actual data.  In this manner we can work with what you have and not Digalo's data and better understand your situation without having to guess at what you file looks like.

Avatar
Tony Newnham
Member
Members
Level 0
Forum Posts: 8
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
5
July 21, 2021 - 8:04 pm
sp_Permalink sp_Print sp_EditHistory

Hi Alan,

3 sheets attached

Sample source data that has my cleaning query

Refence table for "Makes", i have cut this down to about a dozen examples but is normally passing through 1,200 lines and will adjust over time as I know I have some more cleaning work to get it right 

Refence table for "Categories and Sub Categories", this one is pretty much the full list but may change slightly over time.

 

In the sample data, my next steps are:

Add a new new column for Make from the Make and Model column using the Makes reference list

Add a new new column for Cat,SubCat from the Make and Model column using the Cat,SubCat reference list

 

Appreciate any ideas

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 174
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
6
July 22, 2021 - 2:26 am
sp_Permalink sp_Print

This does not look like a PQ issue but rather a VBA type solution.  I will be looking into writing some VBA code later today to address this.

It may be more efficient using Arrays but that is not my forte.  I will do this with loops which will be a bit slower.  I envision using the the Instr function within VBA.

More to come later today.

 

Alan

Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 174
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
7
July 22, 2021 - 3:21 am
sp_Permalink sp_Print

For the make and Model, still in PQ

[code]

#"Added Conditional Column14" = Table.AddColumn(#"Renamed Columns - Make and Model", "Make", each if Text.Contains([Description], "CATERPILLAR") then "Caterpillar" else if Text.Contains([Description], "JOHN DEERE") then "John Deere" else if Text.Contains([Description], "VOLVO") then "Volvo" else if Text.Contains([Description], "KOMATSU") then "Komatsu" else if Text.Contains([Description], "BOBCAT") then "Bobcat" else if Text.Contains([Description], "GENIE") then "Genie" else if Text.Contains([Description], "TOYOTA") then "Toyota" else if Text.Contains([Description], "JLG") then "JLG" else if Text.Contains([Description], "JCB") then "JCB" else if Text.Contains([Description], "CASE") then "Case" else if Text.Contains([Description], "FORD") then "Ford" else if Text.Contains([Description], "MACK") then "Kenworth" else if Text.Contains([Description], "INTERNATIONAL") then "International" else if Text.Contains([Description], "MITSUBISHI") then "Mitsubishi" else if Text.Contains([Description], "KENWORTH") then "Kenworth" else "Other")
[/code]

 

Note that this is case sensitive.

Cannot help with other issue as there are to many.  VBA may be a solution, but it may take a long time to process a thousand rows.

Avatar
Tony Newnham
Member
Members
Level 0
Forum Posts: 8
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
8
July 23, 2021 - 5:07 pm
sp_Permalink sp_Print

Thanks again Alan,

FYI - I ran the merge tables with a real data set of 500 lines and then tried the full list of Keywords for Makes and Categories and each of those done separately grew out about 600,000 & 400,000 rows of data. It ran surprisingly quickly but also highlighted the problem in my keywords data such as sub categories for Cabin and also Cabin & Chassis that are both relevant but create two entries so in the end makes for more cleaning.

Thanks for conditional column code, that list of makes is just on 1,200 so I will test a Concat built out code for all 1,200 and see how that goes and will keep looking for a solution.

Cheers

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1548
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
9
July 25, 2021 - 8:05 pm
sp_Permalink sp_Print

Hi Tony,

You can use List.Intersect to retrieve the make

List.Intersect( { Text.Split(Text.Proper([Make and Model])," "), Makes[Make]}){0} 

But the problem with the Category and SubCategory, as you've discovered, is that looking up a word can return multiple matches e.g. Auger matches Auger and Auger Bit; Boat matches Boat, Boat Engine, Boat Trailer etc.  I haven't figured out a way to resolve that problem.  Without knowing which part(s) of the Make and Model string to lookup, it's very difficult.

Regards

Phil

Avatar
Tony Newnham
Member
Members
Level 0
Forum Posts: 8
Member Since:
June 29, 2021
sp_UserOfflineSmall Offline
10
July 27, 2021 - 12:10 am
sp_Permalink sp_Print

Amazing Phil,

Thanks for that, works a treat and has shown me about half a dozen new steps / tricks / methods that I would have taken a long time to find.

Awesome.

Cheers Tony

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1548
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
11
July 27, 2021 - 9:00 am
sp_Permalink sp_Print

No worries.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Marcel Bila, Rick Scott, Leigh Thomas, Sheilah Taylor
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Sheilah Taylor
Clare Webber
David Jenssen
Dominic Brosnahan
Young You
Jennifer Owens
Mohamed Touahria
Sheila McCall
Nicholas Montano
John Babcock
Forum Stats:
Groups: 3
Forums: 24
Topics: 6524
Posts: 28551

 

Member Stats:
Guest Posters: 49
Members: 32804
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.