Categorization by using Key words

[Anonymous]

I have 2 table contains 
table 1> Course Title And Its Old Catagory 

Course Name	Old Category
RA Tech External - Certifications
RA Tech External - Certification
Approval Flow U47
RA Tech - RA GRAD - Welcome and RA Overview
AUDIT DEPARTMENT MEETING - LIVE STREAMING - ALL INDIA
RA Prof - RA GRAD – Cracking the Collaboration Code
Testing deployment_1
Testing deployment_2
RA Prof - RA GRAD - The Road Back to You
RA Tech - RA GRAD - Business Process Flow & Process Documentation
RA Prof - RA GRAD - Know Your Talent Policies
RA Tech - RA GRAD - IA Methodology
RA Prof - Unraveling your learning Journey
IN CONS - Oracle Financials Cloud: Payables 2020 Implementation Essentials
RA Tech - RA GRAD - Report Writing
RA Prof - RA GRAD – Discovering your core values
RA Tech - RA GRAD - Standard Operating Procedures
IN CONS - AWS TRAINING
IN CONS - AWS CERTIFIED SOLUTIONS ARCHITECT
RADC PORTAL LEARNING  INCLUDING  ACEC PORTAL ( 2020 )
COMPILATION / STATUTORY DUES ( 2020 )
CONFIRMATION (SA 505) ( 2020 )
I CONFIRM ( 2020 )
AUDITING ACCOUNTS RECEIVABLE ( 2020 )	Finance
AUDITING OPERATING EXPENSES AND COST OF GOODS SOLD ( 2020 )
AUDITING PAYROLL EXPENSE AND RELATED ACCRUALS ( 2020 )
FINANCIAL STATEMENT TICK & TIE ( 2020 )
AUDITING INVESTMENTS IN DEBT AND EQUITY SECURITIES ( 2020 )
PRELIMINARY ANALYTICAL REVIEW ( 2020 )
CONCLUDING ANALYTICAL REVIEW ( 2020 )
TAX AUDIT : TICK & TIE ( 2020 )
AUDITING POINT OF SALE REVENUE ( 2020 )
DWW REVENUE - SHIP & BILL ( 2020 )
IN Industry - FINACE (Banking Elementary) - Know Your Customer (KYC)	Finance
RA Industry - FINACE (Insurance Elementary) - Course	Finance
IN Industry - FIN:ACE (Banking Elementary) - Course 1	Finance
Saba release U48 Nov 7
RA Tech - RA GRAD: Presentation Skills
ACEC - CPE Program
RA Tech - RA GRAD - Third Party Risk Management
RA Tech - RA GRAD: Client Interaction & Interviewing Skills
RA Tech - Facilitator Learning Hours - 0.5
RA Tech - Facilitator Learning Hours - 1
RA Tech - Facilitator Learning Hours - 2
RA Tech - Facilitator Learning Hours - 3
RA Tech - Facilitator Learning Hours - 4
RA Tech - Facilitator Learning Hours - 5
RA Tech - Facilitator Learning Hours - 6
RA Tech - Facilitator Learning Hours - 10
IN Industry - FINACE (Banking Intermediate) - Interim Training	Finance
IN Industry - FINACE (Banking Intermediate) - Swift Reconciliation	Finance
IN Industry - FINACE (Banking Intermediate) - Remittances	Finance
IN Industry - FINACE (Banking Intermediate) - Internal Audit Transformation	Finance
IN Industry - FINACE (Banking Intermediate) - Data Migration	Finance
IN Industry - FINACE (Banking Intermediate) - System Functionality Reviews	Finance
IN Industry - FINACE (Banking Intermediate) - Interchange Merchant Operations	Finance
IN Industry - FINACE (Banking Virtuoso) - Credit risk management	Finance
IN Industry - FINACE (Banking Virtuoso) - Internal Control Accounts & Reconciliation	Finance
IN Industry - FINACE (Banking Virtuoso) - Trade Finance	Finance
IN Industry - FINACE (Banking Virtuoso) - RBS Tranche	Finance
IN Industry - FINACE (Banking Virtuoso) - Priority Sector Lending	Finance
TEST FY22 Ethics refresher (Global Final assessment version)
RA Tech External - Certifications
RA Tech - Facilitator Learning Hours - 8
Icount/ Physical Verification
RA Tech External - OpenSAP Certification	Hot Skills
RA Tech External - CRTP Certification
RA Tech - RA GRAD: MS Excel
IN CONS - Salesforce Administrator certification
IN CONS - Outsystems
IN CONS - SAP Certified Application Associate - SAP SuccessFactors Compensation 2H/2020	Hot Skills
IN CONS - SAP Certified Application Associate - SAP SuccessFactors for Employee Central Payroll	Hot Skills
IN CONS - SAP Certified Application Associate - SAP SuccessFactors Performance and Goal Management Q4/2019	Hot Skills
IN CONS - SAP Certified Application Associate - SAP SuccessFactors Recruiting: Recruiter Experience	Hot Skills
IN CONS - SAP Cloud Platform Integration	Hot Skills
IN Industry - FINACE (Insurance Elementary) - Beginners guide to Life Insurance	Finance
IN Industry - FINACE (Insurance Intermediate) - Agency Sales	Finance
IN Industry - FINACE (Insurance Virtuoso) - Reinsurance	Finance
IN CONS - Analyzing Data with Power BI ,DA-100
IN CONS - Developing Solutions for Microsoft Azure, AZ-204
IN CONS - MATD: Microsoft Azure Fundamentals, AZ 900
IN CONS - Microsoft Azure Administrator, AZ-104
IN CONS - Microsoft Azure Data Fundamentals, DP-900
IN Industry - FIN:ACE (Treasury) - Forex & Derivatives	Finance
IN Industry - FINACE (Treasury) - Fixed Income and Money	Finance
IN CONS - Microsoft Azure
IN CONS - PRINCE2 Agile Foundation & Practitioner certification course
IN CONS - PROJECT MANAGEMENT PROFESSIONAL, PMI
RA Tech External - AWS
Professionalism and teaming
Personal productivity and project management
Business writing and audit documentation
RA Tech External - Certification
IN CONS - Executive MBA –IMT Ghaziabad
IN CONS - Salesforce Certified Administrator

Table 2> Catagory Hot Key Words 

Hot Skills	Fianace	Professional
SAP	Accounts	External
Azure	Fianace	GRAD
Tech	Banking	Profession
Power BI		RA Tech

I need DAX Function to make new Coloumn As a new Catagory by using Hot Key word. its not possible to make If conditions by keeping each and every key word to making catagory 

I need  Solution like if any key words match of Hot Skills in course name need to be catagorised as HOT SKILLS
 In the same way remaining also catagorised with the respective key words. in the form of if Table 1>Cell 1 conatins any key from Table 2 that should be catagorised with respective coloumn heading.

even i tried in this way but it not giving value properly it acting row wise contest 

Please give me a solution to make this automation process

[v-luwang-msft · Accepted solution]

Hi  @Anonymous 

Pls test the below :

Use Power query ,custom new column:

List.ContainsAny(
Text.Split([#"Course Name "], " "),
Table2[#"Hot Skills"]
)

 And then custom new column:

= Table.AddColumn(#"Added Custom2", "New categrey", each if [Hot SkillsCheck] = true then "Hot Skills" else if [FianaceCheck] = true then "Fianace" else if [ProfessionalCheck] = true then "Professional" else "")

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Lucien