Forum Discussion

Douglas Riggins's avatar
Douglas Riggins
Copper Contributor
Apr 23, 2017

Date and time formating not preserved on close and load since April update

Has anyone had a problem with date and time formats not being preserved in the downloaded table since the April Update?

 

I'm using Excel 2016 and all of my Get & Transform queries that are supposed to have columns with date and time formats in the resulting table started displaying the data in number format when the queries refreshed.  I have double checked to ensure that the the queries explicitly set the data types of the date/time columns.  I have also checked to ensure the Preserve Cell Formatting option is turned on for the tables.

 

This seemed to start after the April update was installled.

 

Is there a fix for this?  It's getting rather tedious having to reformat the columns after every update.

BI & Data Analytics
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 24, 2017

    Hi Douglas,

     

    I'm on Excel build 16.0.8027.1008 (O365 8067.2018) and have no such issues

     

  • Piotr Zielinski's avatar
    Piotr Zielinski
    Copper Contributor
    Jun 08, 2018

     

    I know this is old entry but...

    We are actually facing the same issue. I've recently moved to Office 2016 and all the reports we've built in Excel 2010 + Power Query are impacted by this.

    The date format (set explicitly in last step of the query) is not applied in the data loaded in worksheet.

    To be more precise:

    After first load of new query it's Ok. The problem starts when you try to change the order of the columns or run the query again with some modifications.

     

    Next observation - the checkboxes in the "external data properties" window seem to have no effect in Excel 2016, ex. when the "preserve column sort" is marked, the columns keep to go back to the original (query) order after each update.

     

    Anyone else faced this issue?

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jun 08, 2018

      Hi Piotr,

       

      IMHO, external data properties work in Excel 2016. If, for example, the query returns such table

      with the settings

      and after the sorting the column in descending order (same for the filtering)

      the query refresh won't update your sorting/filtering. However, If you change the order of the columns in the query and update you'll have unsorted/unfiltered table. And formats will be applied correctly.

      If you uncheck Preserve sort/layout and change column order in query that will be like

      query lost column layout but keep cells format for initial columns.

      If uncheck another setting keeping first checked

      and change the order of the columns in query you will have correct formatting on changed columns order.

Resources