none
PowerPivot doesn't pass through date format to Excel

    Question

  • I've imported a simple CSV file into PowerPivot.  I had to change one column's Data Type to DATE from TEXT.  My Excel workbook prompted me for a refresh.  However, I am still only able to use the text filtering within my pivot.  The Date Filters option is not available under filters.

    How can I get PowerPivot to pass the date format through to the workbook?  I've been reading help blogs for hours and have only found this issue to be unresolved for quite some time.  How do I correct the issue?

    Saturday, February 11, 2012 6:09 PM

Answers

All replies

  • Hi,

    It seems that CSV will treat the Data Column as TEXT, PowerPivot will keep it same as CSV, to workaround the issue, you can convert it to Date type manually just as you mentioned, besides, you can leverage SSIS to import the CSV into Database table, make sure the type of the column in the database table is Date, then import the table into PowerPivot.


    Challen Fu

    TechNet Community Support

    Monday, February 13, 2012 2:31 AM
  • Challen,

    I'm working on my home PC, so I don't have SSIS to take advantage of. I need to point to a flat text-only file as my data source. That is why I was trying to utilize PowerPivot's ability to use a character based file with ,"10/01/2011", as one of the date fields. I did try importing the database into Access and imported the field as date. Access allowed a date filter on the field, however, when I pointed PowerPivot to the Access.db, I still had the same issue. PowerPivot recognized the date format from Access. But, the problem still exists where the date filter is unavailable within PowerPivot. This really should be working even without going through Access. A direct character based file should be a legitimate data source.  How can I correct this?

    Tuesday, February 14, 2012 11:10 AM
  • Hi MightyCoo,

    It seems this is a know issue, even if the type of the column in the PivotTable is Date, there is no Date Filter as in the Excel, there are only two "Label Filter" and "Value Filter", this issue has been submitted at connect, please vote it here: http://connect.microsoft.com/SQLServer/feedback/details/637912/powerpivot-treating-date-columns-as-text

    Similar thread http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/c0253620-6ae0-46ea-8b6e-124e6c950239

    Thanks,


    Challen Fu

    TechNet Community Support

    Thursday, February 16, 2012 6:56 AM