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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
manoj619
New Member

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

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
manoj619_0-1677330160543.png

Expecting output

manoj619_1-1677330193518.png

 

8 REPLIES 8
pankaj_lp
Helper I
Helper 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.

AmiraBedh
Memorable Member
Memorable Member

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.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Thanks for the reply,

but, i am getting error like this when I tried

manoj619_0-1677396882992.png

 

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]))
AmiraBedh_0-1677401705052.png

 

I am attaching the PBI file.


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

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

manoj619_0-1677404127201.png

findtext.pbix

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 ?


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Same error for calculated table also
expected output:

manoj619_0-1677412034752.png

I am attaching the PBI file.

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

Based on the PBI file you provided : 

Table 1 :

AmiraBedh_0-1677492366235.png

Table 2 : 

 

AmiraBedh_1-1677492385974.png

 

 

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 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors