Forum Discussion
Lookup values between two tables
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 AddListIf 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.
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
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.
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😄
You are a superstar 😁