cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

Sure!

This query will match all words from Table2 who are somehow included in the strings of Table1 in a case insensitive mode:

 

let
    Table1 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    Table2 = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Key1],[Key2], Comparer.OrdinalIgnoreCase))),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
    #"Expanded RelativeJoin"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
New Member

Re: joinAlgorithm and keyEqualityComparers

Thank you!

Highlighted
Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

If you run into performance issues with these techniques, it's worth checking out if you can partition your tables: http://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerq...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper IV
Helper IV

Re: joinAlgorithm and keyEqualityComparers

Hi @ImkeF ,

 

Sorry for replying to an old post, but needed your valuable help to my query as this problem looked similar to mine:

 

I have a single column table named WordList containing a unique list of values, whose each word value i want to do a partial match with phrases in multiple Attribute columns in another table named Data Extract and get a count of the item codes that show these word values. I want to count only the First occurence of a word value for each item

 

e.g. A word value may occur in a single phrase multiple times OR across multiple Attributes for that Item, but it should be counted as 1.

 

WordList Table:

 

Words

auto

bus

ingenius

natural

color

 

 

DataExtract Table:

 

ItemID    Attr1                       Attr2                        Attr3                      Attr4

10001      natural and soft     put in auto mode      so natural!               get auto; get auto mode

10002      bus at stop             natural icecream       boy is ingenius        more power

10003      let me out!             promote goodwill     simple simon           auto model

10004      matching color      natural color              natural essence       figure it!

10005      god will                  meet at bus stop       similar cases            rosetta stone

10006      mud ringworm       helper columns         rainbow color          geneva convention

10007      masked man          tenor voice                silica gel                   natural smell

10008      microcosm             guns of navarone      bloated cell              enigmatic man

10009      cellular sky             i am natural               color coated            ingenius of him!

10010      ingenius body        maker chambers       old story                  minor changes

 

 

Output Table:

Words         ItemCount          %ItemCount

auto                   2                              =2/10

bus                     2                              =2/10

ingenius             3                              =3/10

natural               5                              =5/10

color                  3                              =3/10

 


How do i find the Item Counts?

 

Highlighted
Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

not sure what you mean with "partial match":

1) Would "Autobus" return a match (or even 2)?

 

and if so: only for "bus" or also for "auto" - leading to the second question:

 

2) Shall this be case sensitive or not?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper IV
Helper IV

Re: joinAlgorithm and keyEqualityComparers

Hi @ImkeF ,

 

Thanks for your reply. 🙂

 

Sorry, i added a new post later with the complete issue on the Desktop board. Got a solution using DAX in PowerBI, but needed a Power Query solution in Excel as i am on Excel 2016 and as the data is huge.

 

To answer your question - sorry again for the wrong word used ie. partial. What i simply meant is:

 

Only search for each word given in the Wordlist Table in the phrases found in the Attribute columns in DataExtract table. and get a count of the items that have atleast one occurence of each word. Also, it will be case-insensitive.

 

You may reply to that new post. Again, apologies for the replying to an old post, instead of starting a new post.

Highlighted
Advocate II
Advocate II

Re: joinAlgorithm and keyEqualityComparers

That was a super helpful example code @ImkeF, thanks so much! I was able to apply it to my own situation super easily!

Highlighted
New Member

Re: joinAlgorithm and keyEqualityComparers

It looks like the documentation for keyEqualityComparers is the same as the joinOptions for Table.FuzzyJoin

 

https://docs.microsoft.com/en-us/powerquery-m/table-fuzzyjoin

 

I've played with them a little and gotten them to work, i.e.:

 

  #"Merged Queries" = Table.NestedJoin(Source, {"Text1", "Text2"}, #"VCP-Microsoft-Unifications", {"Name", "Text2"}, "VCP Microsoft Unifications", JoinKind.LeftOuter, [IgnoreCase = true, IgnoreSpace = false]),

 

Hope that helps.

 

Zig.

 

Highlighted
Helper III
Helper III

Re: joinAlgorithm and keyEqualityComparers

Hi @ImkeF 

I converted your code into the function below.

 

(LookInTbl as table, KeywordTbl as table, LookInCol as text, KeywordCol as text) =>
// https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

let
    RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
        (Earlier) => Table.SelectRows(KeywordTbl, 
                each Text.Contains(Table.Column(Earlier,LookInCol),Table.Column(KeywordTbl,KeywordCol), Comparer.OrdinalIgnoreCase))),
    ExpandRelativeJoin = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {KeywordCol}, {KeywordCol})
in
    ExpandRelativeJoin

Then called it like this:

RelativeMerge = SearchColForKeywords(GetWordsTbl, KeywordTbl, "English", "Keywords"),  

But it generated this error.

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

 Could you check to see if I wrote something incorrectly?
Thanks!

Highlighted
Super User III
Super User III

Re: joinAlgorithm and keyEqualityComparers

Hi @freelensia ,

a table reference followed by a column name will return a list and not a text (for the first argument of the Text.Contains-function). Hence the error message. To transform my query into a function you have to operate on a record-level like so instead:

 

let
    LookInTbl = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    KeywordTbl = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    LookInCol = "Key1",
    KeywordCol = "Key2",
    fnRelMerge = (LookInTbl as table, KeywordTbl as table, LookInCol as text, KeywordCol as text) =>
    let
            RelativeMerge = Table.AddColumn(LookInTbl, "RelativeJoin", 
            (Earlier) => Table.SelectRows(KeywordTbl, 
                         each Text.Contains(Record.Field(Earlier, LookInCol), Record.Field(_, KeywordCol), Comparer.OrdinalIgnoreCase))),
            #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
    in
        #"Expanded RelativeJoin",
    CallFunction = fnRelMerge(LookInTbl, KeywordTbl, LookInCol, KeywordCol )
in
    CallFunction

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors