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