PowerQuery Lookup Key(s)

[cmcreynolds]

It's 2016 and I want to improve my existing reports. This basically boils down to using text as keys to match up two datasets.

(BTW, I'm running Office 2016 w/PowerQuery built in - so far so good!)

What I did in the past was built a relationship based on these two key fields, then added a dummy variable so I can sort on ONLY those records with connections:

=IF(Table1[Key1]=RELATED(Table2[Key2]),1,0)

Works just fine - unless there's a duplicate in Key2 (which the people entering data don't realize they do, but it's quite often).

So, I was wondering if there's a workaround using LOOKUPVALUE...but if I'm not mistaken, it needs a "result" argument in order to execute, instead of just a "yep, there is a match!" Also, I think if there's a duplicate in my Table2, it'll error out.

Oh - and does the key have to be sorted in order for LOOKUPVALUE to work?

I just want it dummy coded again so I can filter, I don't need to return any values.

Thank you.

 

[PentaGalCXO]

Since there may be duplicates, i suggest you not have a defined relationship between the tables. Then this should work as calculated column:
=if(calculate(countrows(Table2),filter(Table2,Table2[key2]=Table1[Key1]))>1,1,0)

 

[cmcreynolds]

Okay, I'm following your thoughts but I only get 0s right now

 

[cmcreynolds]

Maybe >0 instead of >1?