Add a column under a condition

[Anonymous]

Hello,

I have the following tables:

I aim to take column "A" from the first excel file and compare it with column "C" from the second excel file, if the string part in column "C" is contained in column "A"  (ex AA is contained in AA-11) then I need to add a column in the first excel sheet and fill it with the value of column "D" from the second excel file. (I think I need a nested loop because I need to compare every row from "A" with every row from "C").

I would be very thankful if one could help me.

Thanks

Best Regards

[Vijay_A_Verma]

In a custom column, put following where Table2 is your Lookup table

= try Table2{[C=Text.BeforeDelimiter([A],"-")]}[D] otherwise null

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test 

Table2 Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lFKyc9LVYrViVZyBvE88xQCivLTi1KLi8GCTiBBiDSYEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [C = _t, D = _t])
in
    Source

Table1 Code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUNTRUitUBs4xMwSwnR10LCMvZUddYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each try Table2{[C=Text.BeforeDelimiter([A],"-")]}[D] otherwise null)
in
    #"Added Custom"

[Anonymous]

Thanks a lot for your detailed answer, but the two tables that I put were an example (I forget to mention that).

it's a confidential document so...

When I paste the code of table 2, the confidential table turned on the example table.

Could you please help me

Thanks

[Vijay_A_Verma]

Table2 code is for testing. Just make sure that name of your query is Table2. Don't use Table2 code, only Table1 code. 
Basically, in both Table1 and Table2, you just need to replace the Source with your Source. And make sure Table2 is named Table2 only.

[Anonymous]

Hello again,

I think I am missing something or I don't get something.

I have again paste only the code of Table1  and got  my table turned to the example table.

PS: I am not sure what do you mean by "Replace the Source with your Source"

Thank you

Best regards