Forum Discussion
Power Query - Match text from distinct tables
You can use an approach like this to get the codes for descriptions that include a given code from TableA. This assume you use this measure in a Table visual that has the Code column from TableA. If more than one row in TableB contains that code, it will concatenate the codes together.
Table B Number(s) = VAR tableAcode = SELECTEDVALUE ( TableA[Code] ) VAR matchingBrows = FILTER ( TableB, SEARCH ( tableAcode, TableB[Comments],, 0 ) > 0 ) RETURN IF ( ISBLANK ( COUNTROWS ( matchingBrows ) ), BLANK (), CONCATENATEX ( matchingBrows, TableB[Code], "; " ) )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
You can use an approach like this to get the codes for descriptions that include a given code from TableA. This assume you use this measure in a Table visual that has the Code column from TableA. If more than one row in TableB contains that code, it will concatenate the codes together.
Table B Number(s) =
VAR tableAcode =
SELECTEDVALUE ( TableA[Code] )
VAR matchingBrows =
FILTER ( TableB, SEARCH ( tableAcode, TableB[Comments],, 0 ) > 0 )
RETURN
IF (
ISBLANK ( COUNTROWS ( matchingBrows ) ),
BLANK (),
CONCATENATEX ( matchingBrows, TableB[Code], "; " )
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
mahoneypat , thank you for your reply.
To be able to use this formula I had to add a new column in the table B and a called it “Operation”. Then it worked.
Here is the link to the pbix test file where I also mixed codes with the texts cells as a simulation:
https://www.dropbox.com/preview/Power%20BI/Test%20sample.pbix?role=personal
The formula is resulting a field called “Table B Number(s)”, and I just have some question:
- About the column “Operation”, is it really needed or Am I not executing your instruction in the right way?
- Should the field “Table B Number(s)” to have a visual result? Because it is coming empty and if I remove it from the table, it will result in error.
- In the formula I’m only matching the field “Description”, would be possible to match also the field “Comments” in the same formula.
Feel free to modify the attached pbix.
BR//Marcelo
I was not able to access the pbix file, even though I have a Dropbox account. Can you put it on OneDrive or Google Drive?
Regards,
Pat
Hello Pat,
Here is the link to Google drive:
https://drive.google.com/file/d/1iLf4l5wEcAsrH0ec4WR0BWcY6MowhGc8/view?usp=sharing
BR//Marcelo
The issue was that you didn't have Ticket in the CONCATENATEX. Here it is corrected. I also made it so it searches both the Description and Comments columns.
Table B Number(s) = VAR tableAcode = SELECTEDVALUE ( 'Table A Codes Brazil'[Operation Code (CD)] ) VAR matchingBrows = FILTER ( 'Table B Cases Brazil', OR ( SEARCH ( tableAcode, 'Table B Cases Brazil'[Comments],, 0 ) > 0, SEARCH ( tableAcode, 'Table B Cases Brazil'[Description Complaint],, 0 ) > 0 ) ) RETURN IF ( ISBLANK ( COUNTROWS ( matchingBrows ) ), BLANK (), CONCATENATEX ( matchingBrows, 'Table B Cases Brazil'[Ticket], "; " ) )Here is a pic of what it should look like in a table visual with the TableA code number. It returns all matching B Ticket numbers that include that code in either Description or Comments fields.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat