Forum Discussion

Anna_Anna's avatar
Anna_Anna
New Member
3 years ago
Solved

Lookup between tables in excel with Power Query

Hello,

I have 3 tables.

All are loaded to the data model.

My goal is to add a column to the main report with the transaction type

First table - Report

The 'Text' column contains a transaction code and some wording

Text

Amount

00000066819 INT NIS DP

268.04

00000066819 NIS DEPO

228,568.14

00000066819 INT NIS DP - April recon

268.04

00000000476 CASH SERVICES FEES

-27.50

Second table – Index_Table to convert the code that appears in the 'Text' column in the first table

Transaction Code

Transaction Name

00000066819

Deposit

00000066827

Deposit

00000034798

Direct Debit

221501550027

Conversion

However regardless to the transaction code, if the 'Text' contains any of the keywords from the third table, the transaction name should be 'Fee' (as the transaction code is the same for the main transaction and for the fee attached to it, but I do want to distinguish between them).

So the third table – Fee_Key_Words looks like this:

 

Key word

Transaction Name

FEE

FEE

TRANSFER

FEE

CHRGS

FEE

So the purpose would be to:

  1. Look in the 'Report' table in the 'Text' column for "Fee" keywords.
  2. If it founds it , in a new column named "Transaction Name" will say "Fee"
  3. If fee keyword is not found, we move to search the transaction code from the 'Text' column in the 'Index_Table'

Thank you!

 

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    3 years ago

    Hi, Anna_Anna 

     

    you need to create a custome colum first. which will check if the text column contains fee key words. 

     

    sample code for custom column in the report table,

     

    let
    currentText = [Text],
    feeKeywords = Table.Column(Fee_Key_Words, "Key word"),
    isFee = List.AnyTrue(List.Transform(feeKeywords, each Text.Contains(currentText, _ , Comparer.OrdinalIgnoreCase)))
    in
    if isFee then "FEE" else null

     

    then you will have to create another column that extracts the transaction codes only, sample code,

     

    Text.Start([Text],11)

     

    This will extract the first 11 characters from the Text column, assuming all your transaction codes are 11 characters long.

    1. Now, let's merge the Report table with the Index_Table. You can do this by going to the Home tab, clicking on Merge Queries, and then Merge Queries as New. Choose the Report table as the first table and Index_Table as the second. For the join kind, choose Left Outer (all from first, matching from second).

     

    2. In the Merge Queries dialog box, match the transaction code column you created in the Report table with the Transaction Code column in the Index_Table. Click OK.

    3. In your newly created table, click on the double-arrow icon in the Index_Table column header. This will expand the column and show you the Transaction Name.

     

     

    4. Finally, create another custom column that will check if the Transaction Name from the Fee_Key_Words is "FEE". If not, it will use the Transaction Name from the Index_Table. Here's the code:

     

    if [FeeKeyWordTransactionName] = "FEE" then "FEE" else [IndexTableTransactionName]

     

     

     

     

4 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hello, Anna_Anna 

    let
    first = your_first_table,
    second = your_second_table,
    third = List.Buffer(your_third_table[Key word]),
    sec_rec = Record.FromList(second[Transaction Name], second[Transaction Code]),
    out = 
        Table.AddColumn(
            first, "Transaction Name",
            (x) => 
                [a = Splitter.SplitTextByDelimiter(" ")(x[Text]),
                b = if List.ContainsAny(a, third) then "Fee" else Record.FieldOrDefault(sec_rec, a{0}, "not found")][b]
        )
in
    out
  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    3 years ago

    Hi, Anna_Anna 

     

    you need to create a custome colum first. which will check if the text column contains fee key words. 

     

    sample code for custom column in the report table,

     

    let
    currentText = [Text],
    feeKeywords = Table.Column(Fee_Key_Words, "Key word"),
    isFee = List.AnyTrue(List.Transform(feeKeywords, each Text.Contains(currentText, _ , Comparer.OrdinalIgnoreCase)))
    in
    if isFee then "FEE" else null

     

    then you will have to create another column that extracts the transaction codes only, sample code,

     

    Text.Start([Text],11)

     

    This will extract the first 11 characters from the Text column, assuming all your transaction codes are 11 characters long.

    1. Now, let's merge the Report table with the Index_Table. You can do this by going to the Home tab, clicking on Merge Queries, and then Merge Queries as New. Choose the Report table as the first table and Index_Table as the second. For the join kind, choose Left Outer (all from first, matching from second).

     

    2. In the Merge Queries dialog box, match the transaction code column you created in the Report table with the Transaction Code column in the Index_Table. Click OK.

    3. In your newly created table, click on the double-arrow icon in the Index_Table column header. This will expand the column and show you the Transaction Name.

     

     

    4. Finally, create another custom column that will check if the Transaction Name from the Fee_Key_Words is "FEE". If not, it will use the Transaction Name from the Index_Table. Here's the code:

     

    if [FeeKeyWordTransactionName] = "FEE" then "FEE" else [IndexTableTransactionName]