Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

keyEqualityComparers

  • 1.  keyEqualityComparers

    Silver Contributor
    Posted Nov 09, 2019 04:26 AM
    Anybody know how to use this? M/S Documentation is non-existent!

    I'm trying to join to two tables, on email address, but fails as default match is case sensitive, so (ideally) want to use keyEqualityComparers

    How do you use it? Does it work? Is it slow?


    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: keyEqualityComparers

    Top Contributor
    Posted Nov 09, 2019 12:46 PM
    If there is no documentation, people probably aren't using it or even know it exists.

    You are trying to join and it is failing.
    What do you mean by failing? Does it give an error? Or do you get no results?

    If the default match is case sensitive, then you can also run one of the case transforms on one or both sides of the join.
    This will force the cases to match.


    However, poking around on the Microsoft forums, I found this as a parameter:
    Comparer.OrdinalIgnoreCase

    That may be what you are looking for.
    Read this thread:
    https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/td-p/110108/page/2

    On page 2, there is an example of this parameter used in a relative join, so that you can see where to put it.
    You may not need a relative join, but the syntax should be similar.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: keyEqualityComparers

    Silver Contributor
    Posted Nov 11, 2019 05:14 AM
    When I said failed, I meant failed to match i.e. "Bob" is different to "bob", in the context of a table join

    I think you're right; maybe keyEqualityComparers isn't useable, as documented, so transforming the data (to lowercase) before is probably the only way

    I would be concerned that the transform might be slow?

    If anyone else has got this to work, I'd be glad to hear about it


    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------

    Conference-PBI_200x200


  • 4.  RE: keyEqualityComparers

    Top Contributor
    Posted Nov 11, 2019 09:39 AM
    Did you try the suggestion in the link?
    That looked very promising - worked fine with a relative join, so I don't see why it wouldn't work with a normal one.

    In any case, if you are worried about performance on the case transform, you can always push it back to SQL and let the database handle that processing.
    In my experience the transforms aren't terribly expensive, but that will depend a lot on your environment.

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    LeapFrogBI
    Portland OR
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: keyEqualityComparers

    Silver Contributor
    Posted Nov 12, 2019 04:07 AM
    I did, but couldn't get syntax right, as just guessing really - which is the problem in the first place. Any ideas? I'm using Table.Join

    But I've managed to circumvent by pre-processing my data, to lower case, which is OK. Thanks for suggestions.

    ------------------------------
    John Dawson
    Software Engineer
    876899077
    ------------------------------

    Conference-PBI_200x200