Log In
Register
Home
Active Member
March 25, 2020
Offline
My office upgraded to Office 365 and the existing queries we have are not functioning as they were before. The date column is being modified to return dates in the General format, vs the Date format. This causes issues in every file that uses the date column.
It seems to be applying this format to new rows after refreshing the data (i.e. it is not applied to rows that already existed in the file prior to the refresh). Below is an image of an example of what the dates look like after a refresh.
I reverted back to Excel 2016, and the queries refresh without any changes to the date formats, so I am assuming this is an issue in Office 365.
Anyone else experiencing this issue or have any recommendations? Queries can be rebuilt, but this is not efficient as we use a large number of them in several files.
July 16, 2010
Offline
Hi Frank,
Welcome to our forum!
Is the data type in the query set to Date?
Mynda
Active Member
March 25, 2020
Offline
Hi Mynda,
Thank you for the response.
Yes the data type is set to Date. I have even tried deleting this query step and creating a new one to set the column as type Date but it did not correct the problem. The confusing part of this is that it does not happen when refreshing the query in Excel 2016; only Excel 365. Also, only some of the values in the column are type General, while others have the correct Date type.
July 16, 2010
Offline
Hi Frank,
We've checked the obvious things so I'm not sure of the cause. I've had this happen from time to time too and I just apply the number format to the query output table in the Excel worksheet and after that the format sticks and is applied to new data.
See if that fixes it.
Mynda
Active Member
March 25, 2020
Offline
That does fix it, however, the table is also used in a PowerPivot measure. The date is used to calculate a weighted average cost and the General format causes the calculation to break.
I tried a process of elimination to identify the problem and it appears this happens when they query is merged with another query. If I remove the merge step, the dates format normally. Maybe this is just a bug.
July 16, 2010
Offline
Hi Frank,
If the data is used in Power Pivot then it should be loaded direct from Power Query to the Data Model/Power Pivot and not loaded to Power Pivot via the worksheet table. Power Query should be loading that column to Power Pivot with the data type set to date.
That said, it sounds like there might be some data in the other table that's being merged that is not able to be set to a date data type, and this is causing the whole column data type to change.
Mynda
1 Guest(s)
