Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
cyborgandy
Helper II
Helper II

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

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:

postcoderegion
AB55Moray
AB56Moray
B1Birmingham
B10Birmingham
B11Birmingham
B12Birmingham
B13Birmingham
B14Birmingham
B15Birmingham
B16Birmingham
B17Birmingham
B18Birmingham
B19Birmingham
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

2 ACCEPTED SOLUTIONS
cyborgandy
Helper II
Helper II

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

View solution in original post

v-yanjiang-msft
Community Support
Community Support

Hi @cyborgandy ,

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

Table1:

vkalyjmsft_0-1654239960963.png

Table2:

vkalyjmsft_1-1654239983089.png

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.

vkalyjmsft_2-1654240172540.png

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.

View solution in original post

5 REPLIES 5
v-yanjiang-msft
Community Support
Community Support

Hi @cyborgandy ,

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

Table1:

vkalyjmsft_0-1654239960963.png

Table2:

vkalyjmsft_1-1654239983089.png

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.

vkalyjmsft_2-1654240172540.png

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
Helper II
Helper II

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

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!

 

 

Hi:

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

In table2 you have:

AB55Moray
AB56Moray

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.

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.