Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kd_pandey
New Member

Wildcard Lookup in Power Query/BI

kd_pandey_0-1713366124219.png

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.

1 ACCEPTED SOLUTION
amustafa
Super User
Super User

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"

 

amustafa_0-1713381660279.png

 

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"

 

amustafa_1-1713381696912.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
amustafa
Super User
Super User

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"

 

amustafa_0-1713381660279.png

 

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"

 

amustafa_1-1713381696912.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors