locked
Case Sensitivity RRS feed

  • Question

  • I think this issue has already been brought up, but I have found no resolution.

    Power Pivot is case insensitive and therefore reports an error for duplicate values in a key field when they should not be considered duplicates. For example, ID numbers from Salesforce are ***EM7F and ***EM7f are being considered the same.

    Please address this issue. Thank you.

    Wednesday, September 9, 2015 3:23 PM

Answers

  • Hi CMcReynolds,

    According to your description, you need to enable case-sensitive primary key for power pivot data model, right?

    PowerPivot automatically picks the collation based on the language you were working on. Collation is what determines the case sensitive nature. The default collation's are case insensitive. Currently, there is no a functionally for us to change collation.

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Thursday, October 8, 2015 7:55 PM
    • Marked as answer by Charlie Liao Monday, October 12, 2015 2:25 AM
    Thursday, September 10, 2015 6:16 AM
  • Hi CMcReynolds,

    Unfortunately, Power Query (or Get & Transform in Excel 2016) doesn't allow us to work around this issue; the value will still end up case-insensitive by the time it reaches the Data Model (i.e. Power Pivot).

    You can submit feedback via Connect here: https://connect.microsoft.com/SQLServer/Feedback. The feedback can be submitted under the Analysis Services category (Power Pivot/Data Model is effectively a sandboxed SQL Server Analysis Services Tabular instance). You'll need to create a Microsoft Connect account if you don't have one already.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, October 8, 2015 9:09 PM
    • Marked as answer by Charlie Liao Monday, October 12, 2015 2:25 AM
    Thursday, October 8, 2015 7:54 PM

All replies

  • Hi CMcReynolds,

    According to your description, you need to enable case-sensitive primary key for power pivot data model, right?

    PowerPivot automatically picks the collation based on the language you were working on. Collation is what determines the case sensitive nature. The default collation's are case insensitive. Currently, there is no a functionally for us to change collation.

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Thursday, October 8, 2015 7:55 PM
    • Marked as answer by Charlie Liao Monday, October 12, 2015 2:25 AM
    Thursday, September 10, 2015 6:16 AM
  • Yes - is there a way for me to adjust that in PowerPivot?

    I'm pulling my data through Power Query, does this make a difference?

    Just for clarification - language as in programming language or as in English vs French? (sorry if that was a stupid question)

    Also, which Category do I put this under in the feedback form? I do not see PowerBI or PowerPivot?

    Thursday, September 10, 2015 1:45 PM
  • Hi CMcReynolds,

    Unfortunately, Power Query (or Get & Transform in Excel 2016) doesn't allow us to work around this issue; the value will still end up case-insensitive by the time it reaches the Data Model (i.e. Power Pivot).

    You can submit feedback via Connect here: https://connect.microsoft.com/SQLServer/Feedback. The feedback can be submitted under the Analysis Services category (Power Pivot/Data Model is effectively a sandboxed SQL Server Analysis Services Tabular instance). You'll need to create a Microsoft Connect account if you don't have one already.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    • Edited by Michael Amadi Thursday, October 8, 2015 9:09 PM
    • Marked as answer by Charlie Liao Monday, October 12, 2015 2:25 AM
    Thursday, October 8, 2015 7:54 PM
  • Hi,

    you can work around it by creating a numerical index as a key in Power Query:

    http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/#more-256


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Michael Amadi Thursday, October 8, 2015 9:05 PM
    Thursday, October 8, 2015 8:43 PM
    Answerer