cmcreynolds
Active Member
- Joined
- May 21, 2015
- Messages
- 295
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.
(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.
Last edited:
