Merge Two Table in Power Query using contains Criteria

[earthworm]

I have imported two tables in Power Query.

I want to merge Table 2 with Table 1 using contains criteria so that i can extract the identified one . The data is huge . Please advice how can i do this in power query.

I cant apply delimited in Table 1 as there is no fix pattern . However the number is present in Table 1

TABLE 1	TABLE 2
123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD	270368024
123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST	270152286
123456 210104 SAMPLE REF:271216832	271216832
123456 210104 SAMPLE REF:271216061	271216061

 

[JEC]

Here an example (only works correctly if there is a single match for each value)

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    lookupTbl = List.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Tekst]),
    result = Table.AddColumn(Source, "Desc", each List.RemoveNulls(List.Transform(lookupTbl,(x)=> if Text.Contains(x,Text.From([Val])) then x else null)){0})
in
    result

Sample would be like this
T1:

Tekst

123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD

123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST

123456 210104 SAMPLE REF:271216832

123456 210104 SAMPLE REF:271216061

T2:

Val

270368024

270152286

271216832

271216061

Query output:

Val	Desc
270368024	123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD
270152286	123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST
271216832	123456 210104 SAMPLE REF:271216832
271216061	123456 210104 SAMPLE REF:271216061

 

[earthworm]

Here an example (only works correctly if there is a single match for each value)

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    lookupTbl = List.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Tekst]),
    result = Table.AddColumn(Source, "Desc", each List.RemoveNulls(List.Transform(lookupTbl,(x)=> if Text.Contains(x,Text.From([Val])) then x else null)){0})
in
    result

Sample would be like this
T1:

Tekst

123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD

123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST

123456 210104 SAMPLE REF:271216832

123456 210104 SAMPLE REF:271216061

T2:

Val

270368024

270152286

271216832

271216061

Query output:

Val	Desc
270368024	123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD
270152286	123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST
271216832	123456 210104 SAMPLE REF:271216832
271216061	123456 210104 SAMPLE REF:271216061

How do you learn this?
Where to write this ?

 

[JEC]

open a blank query and paste this in the advanced editor.

To learn this, you just have to practice,read and test a lot

 

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    lookup = List.Buffer(List.Transform(Excel.CurrentWorkbook(){[Name="Table2"]}[Content][TABLE 2], Text.From)), 
    result = Table.AddColumn(
        Source, 
        "TABLE 2", 
        (x) => List.Skip(lookup, (w) => not Text.Contains(x[TABLE 1], w)){0}?
    )
in
    result