none
PowerPivot dates turn to text in excel RRS feed

  • Question

  • Hi,

    I have seen posts about this type of problem already but I cannot get any dates to work in whatever locale I put my PC.  The scenario is I have a list of dates in SQL or Excel and import them to PowerPivot.  The column type says they are dates and changing the format works nicely changing the month from a number to a written month, so I know powerpivot is seeing these values as dates.  The problem occurs when i create a pivot table in excel with this data.  The dates become text so I have no date filtering ability especially since i would like to use DD/MM/YY formatting.  I have tried changing all regional settings to US and restarting, along with using data in the format M/D/YYYY but no luck.

    I am using Win XP with power pivot 10.50.1600.1 and Excel 2010 version 14.0.4760.1000.

    Can someone please help because I realy like PowerPivot apart from this date problem I am having???

    Thanks

    Toby

    Thursday, October 7, 2010 4:11 PM

Answers

  • Toby,

    Besides Dan's suggestion, I would like to add more comments.

    The format DD/MM/YY by default is under the language English(Canada). You may change the language to it for date type in your Excel file.

    For this issue, based on my test, I reproduce it. It is probably a product issue. If I directly create a PivotTable from an existing table in Excel 2010, when I click the Row Labels, I can see the "Date Filters". However if this is done from PowerPivot, the "Date Filters" is replaced by "Label Filters". I recommend that you submit a feedback to our product team at https://connect.microsoft.com/sql so that they can further investigate this issue and fix it in future.

    Currently you may consider using the following way to work around it:

    Method 1. Inserting a PivotTable from your table in Excel work sheet instead of PowerPivot window.

    If your data source is from SQL Server, you may first import it into an Excel worksheet.

    Method 2. Create an additional column like "OrderDateFilter" in PowerPivot table which uses the expression '=FORMAT([OrderDate],"YYYY-MM-DD")'. Then you can use this column in your PivotTable for label filter.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, October 14, 2010 8:01 AM
    Moderator

All replies

  • How are you trying to filter the data?  Just trying to get a better understanding of what you are seeing that does not appear to be working for you.  Are you using the Label Filters option.  You are right that these values get pulled in as just General format.  To get the filter to work properly you would want the data stored as mm/dd/yyyy so that you have everything padded properly.

    Another option for you to do is simply setup slicers for the different time items like Year, Month, Day, etc.  This might provide a better filtering experience for the user.


    Dan English's BI Blog
    Friday, October 8, 2010 5:01 PM
  • Toby,

    Besides Dan's suggestion, I would like to add more comments.

    The format DD/MM/YY by default is under the language English(Canada). You may change the language to it for date type in your Excel file.

    For this issue, based on my test, I reproduce it. It is probably a product issue. If I directly create a PivotTable from an existing table in Excel 2010, when I click the Row Labels, I can see the "Date Filters". However if this is done from PowerPivot, the "Date Filters" is replaced by "Label Filters". I recommend that you submit a feedback to our product team at https://connect.microsoft.com/sql so that they can further investigate this issue and fix it in future.

    Currently you may consider using the following way to work around it:

    Method 1. Inserting a PivotTable from your table in Excel work sheet instead of PowerPivot window.

    If your data source is from SQL Server, you may first import it into an Excel worksheet.

    Method 2. Create an additional column like "OrderDateFilter" in PowerPivot table which uses the expression '=FORMAT([OrderDate],"YYYY-MM-DD")'. Then you can use this column in your PivotTable for label filter.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, October 14, 2010 8:01 AM
    Moderator
  • Dan,

    I enter the data as MM/dd/yyyy and set all settings to US but still only get the label filters option instead of the date filters option.  I have changed the short date format to MM/dd/yy and still get the same problem.  Within power pivot the field is set to date but it loses this formatting when I create the pivot table in excel.  If I create a standard pivot table from within exel using the same data the date filter is there.

    Thanks

    Toby

    Monday, October 18, 2010 3:51 PM
  • Charles,

    I will report it.

    Please see the above reply to Dan as I can't seem to get the date filters to appear using the format MM/dd/yy.

    Thanks

    Toby

    Monday, October 18, 2010 3:53 PM
  • Correct.  As Charles stated this is definitely a product issue and would be a good candidate for a posting in the Connect.
    Dan English's BI Blog
    Monday, October 18, 2010 3:55 PM
  • I have added this to the Connect site as a bug for a future enhancement - https://connect.microsoft.com/SQLServer/feedback/details/637912/powerpivot-treating-date-columns-as-text.
    Dan English's BI Blog
    Tuesday, January 25, 2011 11:42 AM
  • Hi!

    I seem to having a very similar problem, but not with dates, but with values (monetary amounts, that come through powerpivot initially as text.  when I change them to decimal values, and go into a pivot table with the fields in excel, some of the fields are not available to be added to the values section of the pivot, they are only seen as text.  it happens more frequently on calculated fields, but does not seem to discriminate.  I have yet to get all my fields available on one pivot, although when i create new pivots from same data, i can sometimes get additional or different fields. 

    Saturday, January 29, 2011 5:12 AM
  • Hi,

    Just to add on to this thread. I just tried to replicate the problem with my data and I notice something. If the value is presented as row labels, it will become text. But if I were to shift the same column to the Values section, it becomes values. Hope my observation will help to pin down the problem.

     

    Friday, March 4, 2011 7:42 AM
  • Here is my dilemma; when I connect to an SQL table PowerPivot changes my dates from Datetime format to a Text format. When sorting the dates in my Pivot Table the months and dates are all out of sync with the calendar years. Any suggestions on how to prevent PowerPivot from changing the datetime formatting to text formatting?

    Thursday, May 31, 2012 8:14 PM
  • Has this issue been solved in Office 2013? I'm still using 2010, and the issue has, unbelievably, not been solved yet. Even the Connect entry is screwy (I can't seem to log in to comment - does anyone else have this problem?). 

    What's the deal? PowerPivot is completely useless to me if it doesn't recognize dates. This seems less like a bug and more of a fatal flaw if the team hasn't fixed this yet.

    Tuesday, March 26, 2013 5:23 AM
  • Does this solve the issue?

    Chris Webb Blog Post

    FWIW I always use 14-Mar-01 as it is totally unambiguous!
    Tuesday, March 26, 2013 8:27 AM
  • Hi,

    I'm just checking in to follow up on this.

    Has this been fixed? Are there new work-around?

    Thanks

    Friday, August 1, 2014 11:41 PM