Need help with multiple IF ISNUMBER SEARCH excel function in Power Query

[kr1sh]

Hi,

I am working with Qualys vulnerability excel data of 30-50k rows where in currently using "if isnumber search" excel function in column-B of the attached file.

There are ~ 28 to 30 different text string from another sheet needs to be compared with a single cell in Titel column and then update column-B.

I need some help to get this done using power query. 

I have attached sample file for your reference and looking power query to achieve this formula.

Unable to attach my file but this is the formula in Col-B of Table1.

=IF(ISNUMBER(SEARCH(CustomCategory!$A$2,$F2)),CustomCategory!$B$2,

IF(ISNUMBER(SEARCH(CustomCategory!$A$3,$F2)),CustomCategory!$B$3,

IF(ISNUMBER(SEARCH(CustomCategory!$A$4,$F2)),CustomCategory!$B$4,

IF(ISNUMBER(SEARCH(CustomCategory!$A$5,$F2)),CustomCategory!$B$5,

IF(ISNUMBER(SEARCH(CustomCategory!$A$6,$F2)),CustomCategory!$B$6,

IF(ISNUMBER(SEARCH(CustomCategory!$A$7,$F2)),CustomCategory!$B$7,

IF(ISNUMBER(SEARCH(CustomCategory!$A$8,$F2)),CustomCategory!$B$8,

IF(ISNUMBER(SEARCH(CustomCategory!$A$9,$F2)),CustomCategory!$B$9,

IF(ISNUMBER(SEARCH(CustomCategory!$A$10,$F2)),CustomCategory!$B$10,

IF(ISNUMBER(SEARCH(CustomCategory!$A$11,$F2)),CustomCategory!$B$11,

IF(ISNUMBER(SEARCH(CustomCategory!$A$12,$F2)),CustomCategory!$B$12,

IF(ISNUMBER(SEARCH(CustomCategory!$A$13,$F2)),CustomCategory!$B$13,

IF(ISNUMBER(SEARCH(CustomCategory!$A$14,$F2)),CustomCategory!$B$14,

IF(ISNUMBER(SEARCH(CustomCategory!$A$15,$F2)),CustomCategory!$B$15,

IF(ISNUMBER(SEARCH(CustomCategory!$A$16,$F2)),CustomCategory!$B$16,

IF(ISNUMBER(SEARCH(CustomCategory!$A$17,$F2)),CustomCategory!$B$17,

IF(ISNUMBER(SEARCH(CustomCategory!$A$18,$F2)),CustomCategory!$B$18,

IF(ISNUMBER(SEARCH(CustomCategory!$A$19,$F2)),CustomCategory!$B$19,

IF(ISNUMBER(SEARCH(CustomCategory!$A$20,$F2)),CustomCategory!$B$20,

IF(ISNUMBER(SEARCH(CustomCategory!$A$21,$F2)),CustomCategory!$B$21,

IF(ISNUMBER(SEARCH(CustomCategory!$A$22,$F2)),CustomCategory!$B$22,

IF(ISNUMBER(SEARCH(CustomCategory!$A$23,$F2)),CustomCategory!$B$23,

IF(ISNUMBER(SEARCH(CustomCategory!$A$24,$F2)),CustomCategory!$B$24,

IF(ISNUMBER(SEARCH(CustomCategory!$A$25,$F2)),CustomCategory!$B$25,

IF(ISNUMBER(SEARCH(CustomCategory!$A$26,$F2)),CustomCategory!$B$26,

IF(ISNUMBER(SEARCH(CustomCategory!$A$27,$F2)),CustomCategory!$B$27,

IF(ISNUMBER(SEARCH(CustomCategory!$A$28,$F2)),CustomCategory!$B$28,"Legacy Patches")))))))))))))))))))))))))))

Table2: CustomCategory

Vulnerability title contains      Custom Category

McAfee	McAfee Vulnerability
July 2020	Current Month Patching
June 2020	Previous Two Months
May 2020	Previous Two Months
MS15-124	Workaround not part of monthly patching
Variant 4	Workaround not part of monthly patching
Graphics Component	Graphics/Font Vulnerability
Font	Graphics/Font Vulnerability
IPv6 Protocol	IPV6 vulnerability
Media Player	Windows media player
Certificates Spoofing	Certificate spoofing vulnerability
Wireshark	Wireshark
Malware Protection Engine	Windows Defender
Amazon	To be moved to opco repsonsibility
domino	To be moved to opco repsonsibility
Ricoh Printer Drivers	Workaround not part of monthly patching
Red Hat	Linux vulnerability on Windows server
Backup	Backup Software
(ADV190013)	Workaround not part of monthly patching
microcode	Intel Microcode patch for Win2016
(BlueKeep)	Current Month Patching
VMware Tools	VM Tools vulnerability
SymCrypt	SymCrypt Vulnerability
Symantec	Symantec Vulnerability
Microsoft Windows Adobe	Legacy Patches
EMC	Backup Software
Zoom	Zoom related Vulnerabilities
Rest others	Legacy Patches

[v-eachen-msft]

Hi @kr1sh ,

Could you please share your sample data and the expected result here if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

[kr1sh]

attached my sample file in google drive, refer column-B in the attachment contain excel formual and expecting same result using power query.

https://docs.google.com/file/d/1FLvb9IyTt7DzeYNPyXyI3MvKDy3ZLuip/edit?usp=docslist_api&filetype=msex...

[kr1sh]

Hi   @Fowmy  / @v-eachen-msft 

Attached my sample file in google drive, refer column-B in the attachment contain excel formual and expecting same result using power query.

https://docs.google.com/file/d/1FLvb9IyTt7DzeYNPyXyI3MvKDy3ZLuip/edit?usp=docslist_api&filetype=msex...

[Fowmy]

@kr1sh 

Save your file in One Drive, Goole Drive, etc. and share the link here. It not clear what you are trying to do with the data.
Give examples as well

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn