need to match data from a different table that includes the text of the current table column

[manoj619]

I have a sysid and a status column in table 1, and I need to search for and retrieve data from table 2's sysid column that includes the text from table 1's sysid column

Expecting output

[pankaj_lp]

1. In the power query, split the first column.

2. Create two duplicates of the table.
3. Delete the first splitted column in the first table and delete the second splitted column in the second table.
4. Append as a new quesry , the duplicates of the table by renaming as same column name.
5. Delete the duplicates.
6. Make the new relaationship between the appended table and the table2.
7. filter the blank values in the id.

[AmiraBedh]

Try the following :

FILTER(
Table2,
SEARCH(
MAX(Table1[sysid]),
Table2[sysid],
,
0
) > 0
)

The MAX function will get the maximum value of the sysid column in Table1, assuming that each sysid appears only once in Table1. 

The SEARCH function will search for the sysid text in the sysid column of Table2. If the sysid text is found, the function returns value different from 0, which is used to filter Table2 using the FILTER function.

Be careful with the SEARCH function since it is case-sensitive, so if you want to perform a case-insensitive search, using FIND can be an alternative.

[manoj619]

Thanks for the reply,

but, i am getting error like this when I tried

[AmiraBedh]

I tried to reproduce what you provided in the screenshot. You need to create a calculated table like below : 

Table 2 = FILTER('Second Table', CONTAINS('First Table', 'First Table'[sysid], 'Second Table'[sysid]))

I am attaching the PBI file.

[manoj619]

I tried again, but if there are multiple texts in the column table, it fails.

findtext.pbix

[AmiraBedh]

You are trying to create a calculated column while in the solution I provided it is a calculated table

Can you provide your expected output ?

[manoj619]

Same error for calculated table also
expected output:

I am attaching the PBI file.

https://1drv.ms/u/s!AsAyziJRKwR0j2sYl-N_7n9TGun8?e=oauTUG

[AmiraBedh]

Based on the PBI file you provided : 

Table 1 :

Table 2 : 

the output will only contain data for the row with sysid=KLS in the table 2 and the status will be DONE.

The other rows will have status null as there is no matching between sysid from Table1 and syside from Table2 for the values HGF, KNF, JKO, IOK