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.
See Answer
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
Active Member
March 25, 2020
Offline
Thank you-I will look at the data in the other table to see if there is anything in it that could be causing the issue.
It is still strange that this is only happening in Excel 365.
Power Query
Excel for Decision Making
November 19, 2015
Offline
Was this problem ever solved? I see this was an issue back in '21. I am having the same problem in 2023. Please advise.
Thank you
Celeste
July 16, 2010
Offline
Hi Celeste,
Are you loading your data from Power Query to Power Pivot or only from Power Query to an Excel Table? If the former, then it should go direct to Power Pivot and shouldn't have this issue.
If to an Excel table, then I suspect something is preventing Excel form detecting they're dates and not applying the automatic formatting it usually does. Not sure why, but I'd try updating Excel to see if a fix is available as it's definitely a bug.
Of course, I'm assuming you've already checked that the date column has the correct 'date' data type assigned to it.
Mynda
Power Query
Excel for Decision Making
November 19, 2015
Offline
I am loading data from Power Query to an Excel table. And yes I did change the data type to date and still nothing.
July 16, 2010
Offline
In that case I'd say it's a bug. You can try updating Excel to see if there's been a fix. If that doesn't work, you're welcome to share your file with me via email and I can raise it with the Excel team at Microsoft directly, assuming I can replicate the issue at my end.
New Member
July 1, 2023
Offline
Celeste,
Had the same frustrating problem and found the solution here:
Power Query Changes Date After Refresh
Basically, have to preserve the table column formatting with this setting so it stays the same upon data refresh.
Answers Post
The following users say thank you to Matthew Malinowski for this useful post:
Mynda TreacyNew Member
May 7, 2024
Offline
Even the solution provided by Matthew has not solved this issue for me. My query was created on Excel 2016 and now my excel is upgraded to 2019. I am loading query to excel table only but the date stays to general number format. Any help would be appreciated. Current workaround i am using is changing data type to date on the output excel table by right click-> format cell-> date
March 5, 2022
Offline
I'm experiencing precisely the same issue after updating from Excel 2016 to Excel 365 on 'institutional' licenses at work. I also load from PowerQuery to Excel tables. It's not affecting all the rows - just the new ones. My current workaround is to select the date columns in the table after loading from PQ, then CTRL-1, and select a Date format.
A side effect is Timelines referencing affected Date columns began disappearing from my worksheets.
I had already tried the table update options (that Matthew mentioned) but without success. However, this thread gave me an idea I will test in the office later this week. What if my dates already had a few formatted "General" when the "Preserve formatting..." option was re-applied. If I recall, those settings were 'reset' to their defaults during the update and I reset them after I began having this issue.
1 Guest(s)
