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

[Douglas Riggins]

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.

[#2]

Hi Douglas,

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

[#3]

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?

[#4]

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.