Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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... 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)
Solved! Go to Solution.
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!
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!
Hi @Anonymous
It'd be great if anyone can help me regarding this! Thank you.
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.
Ooh I see. Is there any particular example that I can refer to?
Here's my proudest work so far:
#"Added Custom" = Table.AddColumn(Index, "Match",
(k) => Table.SelectRows(#"Assignments",
each ([LocID]="*" or k[LocID]=[LocID])
and ([PSA]="*" or k[PSA]=[PSA])
and ([ST ID]="*" or k[ST ID]=[ST ID])
)
),
This does a semi-fuzzy match across three fields of the two tables.
here an exemple of use of List.transformMany and a very basic Fuzzy compare function:
let
ruleList=Table.ToRecords(ruleTab),
tab=Table.FromRecords(List.TransformMany(dataTab[Titles], each ruleList,(x,y)=> if MyFuzzy(x,y[Titles]) then [Titles=x,Function=y[Function]] else [Titles=x,Function=null])),
#"Rimossi duplicati" = Table.Distinct(tab, {"Titles","Function"}),
#"Raggruppate righe" = Table.Group(#"Rimossi duplicati", {"Titles"}, {{"Func", each _[Function]}}),
#"Valori estratti" = Table.TransformColumns(#"Raggruppate righe", {"Func", each Text.Combine(List.Transform(_, Text.From)), type text})
in
#"Valori estratti"
MyFuzzy:
let
myFuzzy=(fieldData,fieldRules)=>
let
listRules=Text.SplitAny(Text.Lower(fieldRules)," -,"),
listData=Text.SplitAny(Text.Lower(fieldData)," -"),
fuzzyMatch=List.ContainsAny(listRules,listData)
in
fuzzyMatch
in
myFuzzy
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHey. 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.
if I understand your need well, you should take a look at the following link
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |