Forum Discussion
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
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
5 Replies
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
