Merge Two Table in Power Query using contains Criteria

earthworm

Well-known Member
Joined
May 19, 2009
Messages
775
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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 1TABLE 2
123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD270368024
123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST270152286
123456 210104 SAMPLE REF:271216832271216832
123456 210104 SAMPLE REF:271216061271216061
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Here an example (only works correctly if there is a single match for each value)

Power Query:
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:

ValDesc
270368024123456 210102 CNL:SAMPLE REF:270368024::BROBINA N AHEED MUHAMMAD
270152286123456 210102 CNL:SAMPLE REF:270152286::MUHAMMAD LUQMAN NO LAST
271216832123456 210104 SAMPLE REF:271216832
271216061123456 210104 SAMPLE REF:271216061
 
Upvote 0
How do you learn this?
Where to write this ?
 
Upvote 0
open a blank query and paste this in the advanced editor.

To learn this, you just have to practice,read and test a lot😉
 
Upvote 0
Power Query:
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
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,132
Members
453,641
Latest member
enfkkviesm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top