Forum Discussion

LesG01stn's avatar
LesG01stn
Frequent Visitor
4 years ago
Solved

Lookup values between two tables

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 fie...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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's avatar
LesG01stn
Frequent Visitor
4 years ago

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's avatar
v-kkf-msft
Community Support
4 years ago

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.