Forum Discussion
Community ChampionjoinAlgorithm and keyEqualityComparers
Now I feel really honoured - thank you guys :-)
Honestly: I haven't recognized these 2 parameters before. And while the MS-documentation holds some things about the joinAlgorithms in here: https://msdn.microsoft.com/en-us/library/mt296614.aspx, there's zero about keyEqualityComparers.
But with regards to the desired relative comparison here (< or >), I dare to say that they probably wouldn't help, as M distinguishes between equality and relational comparisons.
So in order to not let you down here, please have a look at the following query, which performs a relative lookup (and will hopefully appeal to you DAX-masters :-) ):
let Table1 = #table({"Key1"},{{10},{20},{30},{40}}), Table2 = #table({"Key2"},{{9},{19},{29},{39}}), RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", (Earlier) => Table.SelectRows(Table2, each [Key2]<Earlier[Key1])), #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"}) in #"Expanded RelativeJoin"(This is nested row-context: Yes, we have evaluation context in M as well & fortunately it doesn't behave like a moving target :-) )
Community ChampionNow I feel really honoured - thank you guys :-)
Honestly: I haven't recognized these 2 parameters before. And while the MS-documentation holds some things about the joinAlgorithms in here: https://msdn.microsoft.com/en-us/library/mt296614.aspx, there's zero about keyEqualityComparers.
But with regards to the desired relative comparison here (< or >), I dare to say that they probably wouldn't help, as M distinguishes between equality and relational comparisons.
So in order to not let you down here, please have a look at the following query, which performs a relative lookup (and will hopefully appeal to you DAX-masters :-) ):
let
Table1 = #table({"Key1"},{{10},{20},{30},{40}}),
Table2 = #table({"Key2"},{{9},{19},{29},{39}}),
RelativeMerge = Table.AddColumn(Table1, "RelativeJoin",
(Earlier) => Table.SelectRows(Table2,
each [Key2]<Earlier[Key1])),
#"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
#"Expanded RelativeJoin"
(This is nested row-context: Yes, we have evaluation context in M as well & fortunately it doesn't behave like a moving target :-) )
Fantastic example! Thank you for providing it. Do you think the relative merge function you provided below can be modified with a Text.Contains function, so you could essentially do a partial text match merge on two tables?
- ImkeF
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"Thank you!
- ImkeF
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-powerquery-powerbi/
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?- ImkeF
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?
This SAVED ME Major!
It was small, but gave me much to read and apply!
I even added in the Table.FirstN to limit my matches to 1.
let Table1 = Table.Buffer(TransactionsOnly), /* Bank Transactions already cleaed in another query*/ Table2 = Table.Buffer(MatchMatrix), /*Excel File With FROM and TO Columns to be used in my compare, from another query*/ FakeMerge = Table.AddColumn( Table1,"RelMerge", (OrigTable) => Table.FirstN( Table.SelectRows( Table2, each Text.Contains(OrigTable[TheDESCR],[From] ,Comparer.OrdinalIgnoreCase) ),1) /*Limits to first row match*/ ), #"Expanded RelMerge" = Table.ExpandTableColumn(FakeMerge, "RelMerge", {"To"}, {"To"}), #"Replaced Value" = Table.ReplaceValue(#"Expanded RelMerge",null,"Other",Replacer.ReplaceValue,{"To"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Amount", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Source] = " Capital 1 - 1324")) in #"Filtered Rows"
ignore the stuff below I am still troubleshooting somethings, but wanted to share this! very powerful stuff!
With my approach as new transactions appear, I can easily update the excel file and it will now have a new value to test against and convert
