Looking for a solution to migrate keyword search with wildcards in excel to Power Query

[cvonsc]

Hello,

I am currently handling a keyword search in Excel, and I would like to move that functionality to Power Query if possible.  The current structure in Excel is utilizing a wildcard (*) which I understand isn’t possible in Power BI. 

Below is an example of how this is setup where the Vulnerability ID and Title column of Table 2 is checked against the Keyword column in Table 1 and if a match is found, the corresponding fields in columns 2 and 3 are populated from Table 1.  This works but utilizes a lot of resources every time a cell is updated in Excel and right now, I must have every instance of Vulnerability ID and Title added to the sheet to have an exact match in Power BI.  This already includes over 20K rows so the best solution would be to maintain only a keyword table and then have Power Query do the search against only the active vulnerability ID and Titles in the dataset.  

Table 1 (Keywords)
Keyword	Responsible for Mitigation	Type for Report
msft*.NET	Application Owner	.NET
msft*Malware Protection Engine	Platform Services	Microsoft Windows Defender
redhat*firefox	Application Owner	Mozilla FireFox
oracle*nss	Application Owner	Oracle Linux
msft*PowerShell	Platform Services	Microsoft Windows

Table 2 (Vulnerability ID and Title)
Vulnerability ID and Title	Responsible for Mitigation	Type for Report
msft-cve-2017-0160 Microsoft CVE-2017-0160: .NET Framework Remote Code Execution Vulnerability	Application Owner	.NET
msft-cve-2017-11937 Microsoft CVE-2017-11937: Malware Protection Engine Remote Code Execution Vulnerability	Platform Services	Microsoft Windows Defender
redhat_linux-cve-2022-31737 Red Hat: CVE-2022-31737: Important: firefox security update (Multiple Advisories)	Application Owner	Mozilla FireFox
oracle_linux-cve-2023-0767 Oracle Linux: (CVE-2023-0767) (Multiple Advisories): nss security update	Application Owner	Oracle Linux
msft-cve-2022-41076 Microsoft CVE-2022-41076: PowerShell Remote Code Execution Vulnerability	Platform Services	Microsoft Windows

I am fairly new to Power BI and Power Query in general, so I've been struggling to find a solution specific to my data.  Any advise or recommendations on how this could be handled differently would be greatly appreciated.

[AlienSx]

Hello, @cvonsc i'd try smth like this: 

    kwds = List.Buffer(
        List.Transform(
            keywords_table[Keyword], 
            (x) => {Text.BeforeDelimiter(x, "*"), " " & Text.AfterDelimiter(x, "*") & " "}
        )
    ), 
    find_position = (lst, txt) => 
        List.PositionOf(
            lst, 
            txt, 
            Occurrence.First, 
            (x, y) => Text.StartsWith(y, x{0}) and Text.Contains(y, x{1})
    ),
    add_col = Table.AddColumn(
        data_table, 
        "x", 
        (x) => 
            try keywords_table{find_position(kwds, x[Vulnerability ID and Title])}[[Responsible for Mitigation], [Type for Report]]
            otherwise []
    ),
    expand = Table.ExpandRecordColumn(add_col, "x", {"Responsible for Mitigation", "Type for Report"})

where keywords_table and data_table are your Table 1 and Table 2

[cvonsc]

@AlienSx , 

Thank you for the response.  Sorry for the stupid question but I'm trying to figure out how to implement this in my actual report.  Can this all be done in one step by copying and pasting the information above into advanced editor with the updated table names or does this have to be done in several steps?  I keep getting an invalid identifier error and I'm not sure if that is because my column header has the word unique in it or not?

[AlienSx]

name in between [..] must be valid column name. Try to use syntax [ #"your_long_column_name" ]