locked
How to make PowerPivot case sensitive? RRS feed

  • Question

  • Hi all,

    I just discovered that PowerPivot is case-insensitive, which is quite a big problem for me. The only discussion of this I found so far is here: http://dennyglee.com/2010/06/18/powerpivot-you-are-so-insensitive-case-that-is/ .

    That didn't fully answer my question though, is it possible to change this behaviour and make it case sensitive?

    The reason it's a problem for me is that I have a column (from external data source) used as an ID in a relationship, which contains a string of random characters. If it so happens that two entries have an ID which only differs by a case of a letter, everything blows up. More precisely, I can no longer import the data into the table where the column is used as a PK, because it is not unique.

    Thanks for any comments,

    Jurgis

    Thursday, October 21, 2010 3:26 PM

Answers

All replies

  • 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. I don't see any good way of changing collation.


    Thanks, Deva [MSFT] My blog: http://powerpivot.spaces.live.com/
    Friday, October 22, 2010 3:33 PM
  • Thanks for the answer. It still seems to me that this is not the ideal behaviour. In some cases (such as mine) you would want to set case-sensitivity on individual columns. Is it feasible to request such a feature for future verrsions?
    Saturday, October 23, 2010 9:46 AM
  • You can post bugs on Connect.

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, November 3, 2013 11:56 PM
  • Hello,

    I have the same big issue. My key column in the database use uppercase or lowercase to differentiate lines.

    If during the connection, I click on the preview button, the difference between lowercase and upercase is displayed.

    Once data are in powerpivot there is no more difference between upercase and lowercase !

    Where can we post a bug ? I dont'know connect !

    This is definitely a big big issue.

    Thursday, June 19, 2014 4:52 AM
  • Did you ever get this resolved.

    I have a similar issue.   One staff member created new stock codes using lower case, when an upper case existed.  Cannot delete the s/c as transactions exist, and now all Inventory PowerPivots crash.

    Friday, March 10, 2017 3:12 AM
  • I'm not aware of a solution in Power Pivot for this.

    Instead you can import the data through Power Query /Get&Transform and create a new key-column there. Power Query is case-sensitive by default, so it will consider those values as different. I've described the procedure here: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

    Just skip the Remove-Dups-step:

    let
        Source = Excel.CurrentWorkbook(){[Name="YourFactTable"]}[Content],
        // RemoveDups = Table.Distinct(Source, {"DimKey"}),
        AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
        RemoveCols = Table.RemoveColumns(AddIndex,{"FactKey", "FactAttr"})
    in
        RemoveCols

    and continue according to the blogpost.


    Imke Feldmann TheBIccountant.com


    Friday, March 10, 2017 3:45 PM
    Answerer
  • Although PQ is case sensitive, once the data enters the Excel data model all case differences disappear and only the first combination remains.

    If a columns contains abc in the first row and ABC in the 10th row and Abc in th 100th row, all three rows will show abc in the model and this is true for all sources and all methods to enter data into the data model in Excel or in Power BI

    This behavior is consistent from Excel 2010 Power Pivot and I'm trying to find out if there is any chance of fixing it.

    Dany Hoter 

    Tuesday, March 28, 2017 6:04 AM
  • Hi Dany,

    this would be super-helpful (also, because I often see people struggle with this in Power BI as well).

    For me, it would be ideal if we could change case sensitivity like applying types to columns like this for example:

    This would allow us to individually decide at which step of the transformation process we want to disable case sensitivity. I think it is crucial that the default-behaviour in M stays case sensitive, as there are applications out there who rely on that.


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com


    Tuesday, March 28, 2017 7:12 AM
    Answerer