Lookup between tables in excel with Power Query

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]