Sep 21 2020 06:57 AM
Sep 21 2020 06:57 AM
Suddenly one day last week when I refresh my Excel 2016 Power Query file I receive the error "Cannot refresh data type." However, I click OK and data still refreshes but I do not understand how to fix the file (if needed) so the error does not show. Nothing has changed in the query--cannot find anything on Google with this same error. Anyone have an idea why this suddenly started occurring and/or how to fix it?
Oct 05 2020 09:46 PM
I got the same error message and behavior.
Any solution for that?
It is causing issues, when running update with scheduler.
thx a lot.
Oct 06 2020 05:17 AM
Not that I have found and no one else has replied to this post for a possible solution. My Power Query also runs on Task Scheduler but does not seem to interfere with the data refresh. If/when I hear of or find a resolution, I will post it here.
Oct 06 2020 06:03 AM
btw. it is happening on all my excel files when using Refresh All icon.
However I have just realized:
1. when I start Refresh All for the same file, just from another computer, it runs without issues => means the issue seems not to be related to the specific file, rather to the computer or its installation
2. when I start refresh query by right click menu one-by-one query only, it runs without this message => means the issue might be somehow connected to Refresh All functionality...
One note here: computer, where the issue appears, I am accessing via RemoteDesktop. But the other computer I tried and worked well, I am logged in directly/locally. Both computer have exact the same Version of MS Office: Version 2009 (Build 13231.20262)
Oct 06 2020 06:54 AM
1. Yes, it happens on all the files with PowerQuery whether it is through Task Scheduler to run the PowerQuery or manually clicking "Refresh All." My back-up has the same issue with his copies of the same files. My reports have been running without incident for nearly four years so something has changed. In my case I don't think it is the computer. I, too, am working remotely but have been since March--this is a new issue.
2. I have not tried refreshing each Query....there are lot of them...interesting test though.
We are on Windows 10 with the same issue. I am wondering what Data Type changed that it cannot refresh....or as you said might be an issue with Refresh All. I wish MicroSoft would chime in on this!
Oct 08 2020 05:46 AM
Well, I have read more about Excel's Data Types and found the following (will see if I can find the site where I read this information and post it.):
Hope this helps!
Oct 15 2020 04:47 AM
@Woopakay72This is odd but I received the same message this morning on one that was working perfectly with multiple queries. All I had to do was go on the internet. I found that when i am not online i get this error, as soon as I am online and have an internet connection to my pc the error went away. I have no idea why, it is a local file, but the message went away.
Oct 15 2020 04:49 AM
@robertoz I replied earlier, but for me it was as simple as being sure I was online. No idea why as my excel sheet is not online and local, but as soon as I connected to the internet the message went away. The only other thing I did when the message came up this morning was add filter to my master table in excel so thought that the filter did something, but again as soon as I was connected to internet like magic it went away.
Oct 15 2020 05:00 AM
That is odd. I am working remotely and connected to Internet. Doesn't make a difference. I have not yet gone into the office to run it to see if the error goes away. My back-up with copies of the file, receives the same message--he is also working remotely. Does seem strange that one day all works as it has for nearly 4 years, then all of sudden doesn't.
Mar 11 2021 09:25 AM
Not sure if anyone else is seeing this thread still, but it looked like I had some memory that wasn't released when I received this error. No Excel documents were open, but Task Manager showed there was one in the background processes.
I killed the process, reopened and refreshed, didn't get the error again.
Mar 12 2021 12:05 AM
in my case there is no Excel running in the background (Task manager).
And the issue still persists
@Woopakay72: did you solved this already?
I do believe (at least in my case), the issue is not in the XLSx-file itself, as I am able to refresh the same file in its location from another computer without any issue. I do have all the files stored either on Sharepoint online and few on network drive.
Theoretically it might be the issue with internet connection as Johnmarnell reported - already several months I do have an "pseudo-issue" when the system tray icon from network is constantly tellig me "No Internet access", even though I am connected to computer via RDP
...And now I run Troubleshooter for this issue and this is the fist time ever since Win95, the Troubleshooter identified the issue and was able to solve the issue - the tray icon with "No Internet access" disapeared / changed to standard one. BUT what is better, also the data type issue on refresh DISAPEARED as well.
@Johnnmarnell: many thanks for hint
May 25 2021 04:22 AM
The solution of this is simple go to Excel options, Data, Enable data analysis add ins: power pivot.
Now refresh problem solved.
May 28 2021 05:35 AM
Thank you. I also had this issue and your comment helped. Mine also goes away as soon as I am online. Very strange. Either way, I am just happy it works.