Forum Discussion
Merge Queries by text contain
Thank you very much everyone for your input!
I found a simple solution here https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/ and it worked for me perfectly.
Cheers!
You may need to add more details to your post (see links below) but it seems a Fuzzy Match using a Transformation Table would work.
MS defines the transformation table as:
Transformation table – This option allows users to specify another query that holds a mapping table, so that some values can be auto-mapped as part of the matching logic. For example, defining a two-column table with a “From” and “To” text columns with values “Microsoft” and “MSFT” will make these two values be considered the same (similarity score of 1.00) by the matching logic.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
Hey. Apologies because this is my first time posting.
What other information would I need to put up here? Do you mean my whole table, or all the rows for the columns?
I basically have one table query that contains 1000+ contacts with their name, organisations, titles, emails and everything. It's sort of like a contacts dimension table.
Contacts Dimension Table:
| Last Name | Title |
| AAA | CFO |
| BBB | Regional Chief Financial Officer |
| CCC | Senior Manager |
| DDD | Group Chief Officer- New Business Models |
| EEE | Snr Manager, Finance |
| FFF | Head of Human Resources, Asia |
| GGG | Vice President, Group IT Planning & Solution Delivery |
| HHH | Senior Vice President Head of High Impact Team |
| III | Regional Chief Actuary |
| JJJ | Chief Financial Officer |
| KKK | Chief Finance Officer |
| LLL | Chief Risk Officer |
| MMM | VP, Finance |
| NNN | Manager, Group Operational Risk |
| OOO | Tax-Manager |
| PPP | Chief Strategy Officer, Strategy & Transformation |
I have another title dimension table containing the different titles and categories that the titles belong to.
Category-Titles Dimension Table:
| Category | Titles |
| Head of company | CEO |
| Head of company | Managing Director |
| Head of company | General Manager |
| Head of company | Chairman |
| Finance | CFO |
| Finance | Finance |
| Finance | Treasurer |
| Risk | Risk |
| Risk | Actuarial |
| Operations | Operations |
| Strategy | Strategy |
| IT | Information |
| IT | IT |
| Tax | Tax |
| HR | Human Resource |
| HR | HR |
Using this titles dimension table, I would like include the category in the contacts dimension table. However, the issue here is that I am unable to use an exact match for the titles. I'm thinking more of if the title of the contact contains a word of the titles in the title dimension table, the category would be mapped over.
So the end product for example:
| Last Name | Title | Category |
| AAA | CFO | Finance |
| EEE | Snr Manager, Finance | Finance |
and so on.
Hopefully this is much clearer? Thank you. Let me know if anyone needs more information.