Power Query - Match text from distinct tables

[datanau001]

Hello all,

I’m working with two tables “A” and “B”, where in table “A” the main information is a code of 5 digits and in “B” the main information are texts ( problem description and comments related to the problem description).

So, in table “A” I have the tickets created based on the code of five digits and I can measure this.

Therefore, if this code is not ok then another ticket will be created in table B (from another system…) and here in “B” is where is my focus.

How in the texts in “B” I can find the codes from “A” and measure this?

See bellow example:

Table “A”:

Code

17744

Table “B”:

Description:

As ordens de serviço assinadas nesta sexta-feira são mais um exemplo da continuidade das obras de infraestrutura no país, mesmo com a pandemia do novo coronavírus. Nos primeiros 17744 seis meses deste ano, foram 36 inaugurações entre janeiro e junho, sendo 23 entre março e junho, período mais afetado pelo coronavírus. O balanço do primeiro semestre de 2020 representa cerca de R$ 3,5 bilhões realizados em obras.

Comments:

Ainda foi iniciado o serviço de recomposição e proteção de passarelas de pedestres localizadas na BR-060/GO, entre os municípios de Acreúna e Rio Verde. No são sendo realizados os serviços de recuperação e substituição de elementos estruturais, substituição e implantação 17744 de corrimãos, pintura e implantação de novos elementos de sinalização.

So, here is my question:

How can I based in information from “A” (code) find matches in “B” and show “B” tickets number?

I Know that we have possibilities like: customs columns, match function, IF functions,…

But in these tables there are hundred lines with single codes and thousand lines with texts and I would like to know if you have any suggestion.

Regards

Marcelo

[mahoneypat · Accepted solution]

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

[datanau001]

@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

[mahoneypat]

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

[datanau001]

@mahoneypat 

Hello Pat,

Here is the link to Google drive:

https://drive.google.com/file/d/1iLf4l5wEcAsrH0ec4WR0BWcY6MowhGc8/view?usp=sharing

BR//Marcelo

[mahoneypat]

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