Forum Discussion
Date and time formating not preserved on close and load since April update
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?
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.