Forum Discussion
Merge Queries by text contain
Hello everyone, I refer to this query https://community.powerbi.com/t5/Power-Query/Merge-by-Text-Contain-Any-Multiple-values-in-a-row/td-p/745087 as I have a similar issue. (Also Nolock , since you provided the solution)
I have a query containing of contacts' infomation, including the column below (snippet):
| Title |
| CFO |
| Regional Chief Financial Officer |
| Senior Manager |
| Group Chief Officer- New Business Models |
| Snr Manager, Finance |
| Head of Human Resources, Asia |
| Vice President, Group IT Planning & Solution Delivery |
| Senior Vice President Head of High Impact Team |
| Regional Chief Actuary |
| Chief Financial Officer |
| Chief Finance Officer |
| Chief Risk Officer |
| VP, Finance |
| Manager, Group Operational Risk |
| Tax-Manager |
I intend to map in function categories (from another query) using the titles column.
The snippet of the second query is as below:
| Function | Titles |
| Finance | Wealth Management |
| Risk | Underwrit |
| Finance | Treasurer |
| Digital/Innovation | Technology |
| Tax | Tax |
| Strategy | Strategy |
| Strategy | Strategic |
| HR | Staff |
| Risk | Risk |
| Head of company | Principal |
| Head of company | President |
| Strategy | Planning |
| Head of company | Owner |
| Operations | Ops |
| Operations | Operations |
| Operations | Operation |
| Operations | Operating |
As long as the titles in the first query contain words that is in the titles column in second query, the function category should be mapped in to the first query.
After mapping in, my first query should have a title column and also a function column showing the category of the title.
Thank you 🙂
(edited for clarity)
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!
10 Replies
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!
if I understand your need well, you should take a look at the following link
Remember that the table join functions support using other functions as joiner operators. You can do much (MUCH) more than just fuzzy matches. Conditional joins with complex logic over multiple fields - no problem. Imke Feldmann covered that, I can provide examples too.
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 ForumHey. 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.
Hi Anonymous
Is this problem sloved?If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?If not, please feel free to let me know.Best RegardsMaggie
