Merge Queries by text contain

[Anonymous]

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)

[Anonymous · Accepted 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!

[v-juanli-msft]

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 Regards

Maggie

[Anonymous]

It'd be great if anyone can help me regarding this! Thank you.

[lbendlin]

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.

[Anonymous]

Ooh I see. Is there any particular example that I can refer to?

[lbendlin]

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. 

[Anonymous]

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

[edhans]

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

[Anonymous]

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.

[Anonymous]

if I understand your need well, you should take a look at the following link

Replace-Values-in-Column-Conditionally-using-Rules-Table