Searching text in a column for text in another column in a different table

[cyborgandy]

Hi All,

I have two tables.

Table 1: 

Full Postal Code

RG42 9PY

SL1 4WE

GL4 3DL

SL7 1HY

B37 7HQ

RH1 1SH

EC1A 7AJ

Table 2:

postcode	region
AB55	Moray
AB56	Moray
B1	Birmingham
B10	Birmingham
B11	Birmingham
B12	Birmingham
B13	Birmingham
B14	Birmingham
B15	Birmingham
B16	Birmingham
B17	Birmingham
B18	Birmingham
B19	Birmingham
B2	Birmingham

I want to Search post code value from table to Table 1 full and code and wanted a new column in the table 1 which gives me Region of table 2

[cyborgandy · Accepted solution]

I want to Search post code value from table 2 to Table 1 full post code and wanted a new column in the table 1 which gives me Region of table 2

[v-yanjiang-msft · Accepted solution]

Hi @cyborgandy ,

According to your description, I create a sample containing the corresponding value.

Table1:

Table2:

Here's my solution, use the containsstring-function to create a calculated column in Table1.

Column =
MAXX (
    FILTER (
        'Table 2',
        CONTAINSSTRING ( 'Table 1'[Full Postal Code], 'Table 2'[postcode] )
    ),
    'Table 2'[region]
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cyborgandy · Accepted solution]

I want to Search post code value from table 2 to Table 1 full post code and wanted a new column in the table 1 which gives me Region of table 2

[Whitewater100]

Hi:

You can compare the strings but need to know how many letters to match. Here is example of using first three letters .

Calc Col=
var tableonefirst3 = LEFT('Table 1'[FullPostalCode], 3)
var Tabletwofirst3 = LEFT('Table 2'[postcode],3)
return
IF(tableonefirst3 = Tabletwofirst3, 'Table 2'[Region], BLANK())

This is't perfect if you have very similiar entries on both sides. 

I hope this helps!

[Whitewater100]

Hi:

There does not appear to be anything to relate form one table to another. (Based on your example).

In table2 you have:

AB55	Moray
AB56	Moray

and a number of B/Birmingham - I don't see a reference(in part) on Table1?

What is the expected result? Maybe reposting data where there is something in common will help.

[cyborgandy]

Hey basically I have two table one where I have full post code and the 2nd table I have partial post code details with the region I want the region of table to be assigened to table 1 
e.g If AB55 found in table 1 in full post cost row it should give me a region from table 2 of AB55