Wildcard Lookup in Power Query/BI

[kd_pandey]

Hi All,

I need to populate category in first table from "Msg Code" table where "Error Msg" contains "Msg Code". (Power BI Data Modelling)

thanks in advance for your help.

[amustafa · Accepted solution]

In your example, your Error Msg can be linked to multiple Cat. In this case it will get multiple Cat values seprated by a comma. In another case you could get the first Cat matched value. See the attached Excel file and Power Query section and results.

Multiple Matches.

let
    // Load the ErrorMsg table
    SourceErrorMsg = Excel.CurrentWorkbook(){[Name="TableErrorMsg"]}[Content],
    // Load the MsgCode table
    SourceMsgCode = Excel.CurrentWorkbook(){[Name="TableMsgCode"]}[Content],
    // Change column type for Error Msg to text
    #"Changed Type" = Table.TransformColumnTypes(SourceErrorMsg,{{"Error Msg", type text}}),
    
    // Add a custom column to perform the match and concatenate 'Cat' values
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Cat", each 
        let
            CurrentError = [Error Msg],
            // Filter rows in MsgCode table where Msg Code is a part of Error Msg
            FilteredRows = Table.SelectRows(SourceMsgCode, each Text.Contains(CurrentError, [Msg Code])),
            // Concatenate the 'Cat' values if there are matches
            ConcatenatedCats = if Table.IsEmpty(FilteredRows) then null else Text.Combine(FilteredRows[Cat], ", ")
        in
            ConcatenatedCats)
in
    #"Added Custom"

Single Match...

let
    // Load the ErrorMsg table
    SourceErrorMsg = Excel.CurrentWorkbook(){[Name="TableErrorMsg"]}[Content],
    // Load the MsgCode table
    SourceMsgCode = Excel.CurrentWorkbook(){[Name="TableMsgCode"]}[Content],
    // Change column type for Error Msg to text
    #"Changed Type" = Table.TransformColumnTypes(SourceErrorMsg,{{"Error Msg", type text}}),
    
    // Add a custom column to perform the match and get the first 'Cat' value
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Cat", each 
        let
            CurrentError = [Error Msg],
            // Filter rows in MsgCode table where Msg Code is a part of Error Msg
            MatchedRows = Table.SelectRows(SourceMsgCode, each Text.Contains(CurrentError, [Msg Code])),
            // Get the first 'Cat' value if there is a match
            FirstCat = if Table.IsEmpty(MatchedRows) then null else MatchedRows[Cat]{0}
        in
            FirstCat),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Cat", type text}})
in
    #"Changed Type1"