Lookup values between two tables

[LesG01stn]

I have 2 tables. The first table called OrderHeader has a field called ErrorDescription which has very verbose strings. The second table called ErrorLookup has a field called LookFor and a second field called FaultType.

I want to created a custom column in the first table and call it Fault. This column must look up the string from the second table (LookFor) and return FaultType into the new Fault column.

I have gone through many suggestions in this forumn but cannot get any of them to work.

OrderHeader table:

ErrorLookUp table:

Any help will be appreciated! 

[jennratten]

Hello - you would use this in Power Query, not DAX.  Open the Power Query editor and go to the Order Header table.  Click Advanced Editor on the ribbon.  Can you take a screenship of what you see and paste it here?  If it's long I only need the last couple of lines.  Then I will let you know how to incorporate the script that I sent into the Advanced Editor from there.

[LesG01stn]

Thanks so much... I am new and trying to teach myself

let
Source = Excel.Workbook(File.Contents("C:\Users\lesgo\Downloads\Order errors.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Customer", type text}, {"AccountNumber", Int64.Type}, {"OrderCategory", type text}, {"SalesOrderNumber", Int64.Type}, {"SourceChannel", type text}, {"CreatedOn", type datetime}, {"RequestedDeliveryDate", type datetime}, {"PlannedDeliveryDate", type date}, {"TotalAmount", type number}, {"OrderQuantity(Cases)", Int64.Type}, {"CreatedBy", type text}, {"Status", type text}, {"StatusReason", type text}, {"SubmissionStatus", type text}, {"ValidationOutcome", type text}, {"ErrorDescription", type text}, {"IntegrationError", type text}, {"DeliveryLocation", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ErrorDescription] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"OrderQuantity(Cases)", "OrderQuantity"}})
in
#"Renamed Columns"

[jennratten]

You are very welcome.  That's how a lot of us learned.  🙂

Here is the new script.  Note, the last step of your script (before the in) was #"Renamed Columns".  I added a comma to the end of that line, then added the new steps required.

To use this new script, go back to the Advanced Editor for the OrderHeader table and replace the entire contents with the new script below.

let
Source = Excel.Workbook(File.Contents("C:\Users\lesgo\Downloads\Order errors.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Customer", type text}, {"AccountNumber", Int64.Type}, {"OrderCategory", type text}, {"SalesOrderNumber", Int64.Type}, {"SourceChannel", type text}, {"CreatedOn", type datetime}, {"RequestedDeliveryDate", type datetime}, {"PlannedDeliveryDate", type date}, {"TotalAmount", type number}, {"OrderQuantity(Cases)", Int64.Type}, {"CreatedBy", type text}, {"Status", type text}, {"StatusReason", type text}, {"SubmissionStatus", type text}, {"ValidationOutcome", type text}, {"ErrorDescription", type text}, {"IntegrationError", type text}, {"DeliveryLocation", type any}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ErrorDescription] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"OrderQuantity(Cases)", "OrderQuantity"}}),
    varErrorMap = Table.Buffer ( LookFor ),
    varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
    AddList = Table.AddColumn ( #"Renamed Columns", "Fault", (t) => 
        let 
            i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
        in varErrorMap[FaultType]{i}, type text
        )

in
    AddList

[LesG01stn]

Slowly but surely I am getting there... thanks so much for your patience.

Your code adds three columns in my Applied Steps, varErrorMap; varLookFor & AddList.

When I select either of the Varxxx steps I get the following error "Expression.Error: The name 'LookFor' wasn't recognized. Make sure it's spelled correctly."

When I select the new AddList there is a new column called Fault in the table with Function in each row.

If I select Function of any of the rows, a popup dialogue box appears

I then get the following

which takes me to the varErrorMap step... So 'LookFor' is the error.

Every method I have tried in the past gives me this error but I don't know how to rectify??

[v-kkf-msft]

Hi @LesG01stn ,

The 'LookFor' used by @jennratten  in varErrorMap = Table.Buffer ( LookFor ) is actually your ErrorLookup table name, so you need to change the code to

       varErrorMap = Table.Buffer ( ErrorLookup )

Also, when there are no matches, you need to return null, so please change:

       in varErrorMap[FaultType]{i}, type text -> in try varErrorMap[FaultType]{i} otherwise null

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LesG01stn]

OK... now, it is starting to make sense. Thank you so much (also @jennratten), you are making murky waters look a bit clearer.. Unfortunately, I overwrote the report in error, but have recreated from scratch, so the code has changed slightly.

= Table.AddColumn( "Fault" as text, (t) => let 
            i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
        in try varErrorMap[FaultType]{i} otherwise null
        )

This is now what the insert column code looks like, but I get the following error which I know refers to the required formula syntax

This is now the total code

let
Source = Excel.Workbook(File.Contents("C:\Users\lesgo\OneDrive - CCBAGROUP\OrderHeader.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"(Do Not Modify) Order", type text}, {"(Do Not Modify) Row Checksum", type text}, {"(Do Not Modify) Modified On", type datetime}, {"Account Number (Customer) (Account)", Int64.Type}, {"Customer", type text}, {"Order Category", type text}, {"Sales Order Number", Int64.Type}, {"Source Channel", type text}, {"Created On", type datetime}, {"Requested Delivery Date", type datetime}, {"Planned Delivery Date", type date}, {"Total Amount", type number}, {"Order Quantity (Cases)", Int64.Type}, {"Created By", type text}, {"Status", type text}, {"Status Reason", type text}, {"Submission Status", type text}, {"Validation Outcome", type text}, {"Error Description", type text}, {"Integration Error", type text}, {"Delivery Location", type text}, {"Operational Site", type text}, {"Full Name (Account Manager) (Worker)", type text}, {"Sales Office (Customer) (Account)", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(Do Not Modify) Order", "(Do Not Modify) Row Checksum", "(Do Not Modify) Modified On"}),
varErrorMap = Table.Buffer ( ErrorLookUp ),
varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
AddList = Table.AddColumn( "Fault" as text, (t) => let
i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
in try varErrorMap[FaultType]{i} otherwise null
)

in
AddList

[v-kkf-msft]

Hi @LesG01stn ,

The syntax of Table.AddColumn is as follows, the first parameter 'table' is missing in your code. 

Table.AddColumn(table as table, newColumnName as text, columnGenerator as function, optional columnType as nullable type) as table

 So please try the following code.

= Table.AddColumn( #"Removed Columns", "Fault" as text, (t) => let......)

let
Source = Excel.Workbook(File.Contents("C:\Users\lesgo\OneDrive - CCBAGROUP\OrderHeader.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"(Do Not Modify) Order", type text}, {"(Do Not Modify) Row Checksum", type text}, {"(Do Not Modify) Modified On", type datetime}, {"Account Number (Customer) (Account)", Int64.Type}, {"Customer", type text}, {"Order Category", type text}, {"Sales Order Number", Int64.Type}, {"Source Channel", type text}, {"Created On", type datetime}, {"Requested Delivery Date", type datetime}, {"Planned Delivery Date", type date}, {"Total Amount", type number}, {"Order Quantity (Cases)", Int64.Type}, {"Created By", type text}, {"Status", type text}, {"Status Reason", type text}, {"Submission Status", type text}, {"Validation Outcome", type text}, {"Error Description", type text}, {"Integration Error", type text}, {"Delivery Location", type text}, {"Operational Site", type text}, {"Full Name (Account Manager) (Worker)", type text}, {"Sales Office (Customer) (Account)", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(Do Not Modify) Order", "(Do Not Modify) Row Checksum", "(Do Not Modify) Modified On"}),
varErrorMap = Table.Buffer ( ErrorLookUp ),
varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
AddList = Table.AddColumn(#"Removed Columns", "Fault" as text, (t) => let
i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
in try varErrorMap[FaultType]{i} otherwise null
)

in
AddList

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LesG01stn]

Thanks so much... We now get a result BUT

= Table.AddColumn(#"Removed Columns", "Fault" as text, (t) => let
i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
in try varErrorMap[FaultType]{i} otherwise null
)

 only produces null. The function does not lookup the values to return a fault.

Is there any reason why the "*" values were removed from the look for strings? 

varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) )

Let me try explain my logic...

I have a table with sales orders that have been created. In this table there is a field called Error Description. This field has very lengthy strings explaining the error. I want to look up *lookupvalue* from a table called ErrorTypes and if the lookup value is found in the string, then the fault type in the column next to the look up value must be reported

[v-kkf-msft · Accepted solution]

Hi @LesG01stn ,

If the '*' character is not removed, it will look for the text "*API failed*" instead of "API failed". 

I found a wrong column name in the code, please try the following code, it should return the correct value.

let
Source = Excel.Workbook(File.Contents("C:\Users\lesgo\OneDrive - CCBAGROUP\OrderHeader.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"(Do Not Modify) Order", type text}, {"(Do Not Modify) Row Checksum", type text}, {"(Do Not Modify) Modified On", type datetime}, {"Account Number (Customer) (Account)", Int64.Type}, {"Customer", type text}, {"Order Category", type text}, {"Sales Order Number", Int64.Type}, {"Source Channel", type text}, {"Created On", type datetime}, {"Requested Delivery Date", type datetime}, {"Planned Delivery Date", type date}, {"Total Amount", type number}, {"Order Quantity (Cases)", Int64.Type}, {"Created By", type text}, {"Status", type text}, {"Status Reason", type text}, {"Submission Status", type text}, {"Validation Outcome", type text}, {"Error Description", type text}, {"Integration Error", type text}, {"Delivery Location", type text}, {"Operational Site", type text}, {"Full Name (Account Manager) (Worker)", type text}, {"Sales Office (Customer) (Account)", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"(Do Not Modify) Order", "(Do Not Modify) Row Checksum", "(Do Not Modify) Modified On"}),
varErrorMap = Table.Buffer ( ErrorLookUp ),
varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
AddList = Table.AddColumn(#"Removed Columns", "Fault" as text, (t) => let
i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[Error Description] ), _ ) ) ) )
in try varErrorMap[FaultType]{i} otherwise null
)

in
AddList

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LesG01stn]

You are a superstar 😁

[LesG01stn]

@v-kkf-msft @jennratten 

I wanted to thank you both so much... Now that the "coding" you gave me has worked, I have managed to sit down and work out how you guys did what you did and I am now flying with my project😄

[jennratten]

Hello - I was able to do it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcyxDgIhEEXRX3mhNmZjYWG7xsTCWGiHFMiOMhGYDczq74vWJ/daaw63ZRg22zPGShMrxkjhhQvVNwfC6FPCw3OiaYdrJMhM1StLQfQNypkmyKLGraw5cWtcnvAIUtQHxXH/hx5WArcuc5V7oowPa4SUPqzI8lOl3NbGuS8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ErrorDescription = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ErrorDescription", type text}}),
    varErrorMap = Table.Buffer ( LookFor ),
    varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
    AddList = Table.AddColumn ( #"Changed Type", "Fault", (t) => 
        let 
            i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
        in varErrorMap[FaultType]{i}, type text
        )

in
    AddList

[LesG01stn]

I went into the form, selected Modelling > Write DAX expression that creates a new column and pasted your code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcyxDgIhEEXRX3mhNmZjYWG7xsTCWGiHFMiOMhGYDczq74vWJ/daaw63ZRg22zPGShMrxkjhhQvVNwfC6FPCw3OiaYdrJMhM1StLQfQNypkmyKLGraw5cWtcnvAIUtQHxXH/hx5WArcuc5V7oowPa4SUPqzI8lOl3NbGuS8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ErrorDescription = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ErrorDescription", type text}}),
    varErrorMap = Table.Buffer ( LookFor ),
    varLookFor = List.Buffer ( List.Transform ( varErrorMap[LookFor], each Text.Lower ( Text.Replace ( _, "*", "") ) ) ),
    AddList = Table.AddColumn ( #"Changed Type", "Fault", (t) => 
        let 
            i = List.PositionOf ( varLookFor, List.First ( List.Select ( varLookFor, each Text.Contains ( Text.Lower ( t[ErrorDescription] ), _ ) ) ) )
        in varErrorMap[FaultType]{i}, type text
        )

in
    AddList

but I get the following error

[jennratten]

Hi - you will need to add this in Power Query, not DAX.  Can you please go to the Power Query editor, go to the OrderHeader table then click Advanced Editor on the ribbon, take a screen snip of what you see and post it here?  If it is long I only need the last couple of lines.  Then I can let you know exactly how to integrate this solution with your script.