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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
datanau001
Helper III
Helper III

Power Query - Match text from distinct tables

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

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

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

   





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
mahoneypat
Employee
Employee

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

   





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

 

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.

 

CodeMatch.png

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors