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

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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




JD1 Frequent Visitor
Frequent Visitor

Re: joinAlgorithm and keyEqualityComparers

Thank you!

Super User
Super User

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...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




sifar786 Member
Member

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?

 

Super User
Super User

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?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




sifar786 Member
Member

Re: joinAlgorithm and keyEqualityComparers

Hi @ImkeF ,

 

Thanks for your reply. Smiley Happy

 

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.

QuinnP Frequent Visitor
Frequent Visitor

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!

Ziger Visitor
Visitor

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.

 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 312 members 3,538 guests