Forum Discussion
need to match data from a different table that includes the text of the current table column
Expecting output
8 Replies
- AmiraBedh
Super User
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.
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.
pankaj_lpHelper I
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.
