Forum Discussion
Excel 2016 - Power Query Error: "Cannot Refresh Data Type" but data refreshes
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?
15 Replies
I know this is an old thread. I have the error "Unable to refresh data type" and was unable to find a solution from the internet.
Thus, I went through all my data and made sure the data types were consistent for each field of all data connections and queries. For example, I used data "type text" and used "type number" instead of "type any" or other number formats, respectively. Then, after saving, closing and then opening the file, the error no longer appears when clicking Refresh All.
In summary, I think the error in my workbook was due to concatenated data with similar but not identical data types. For example, and integer field with a number field.
The solution of this is simple go to Excel options, Data, Enable data analysis add ins: power pivot.
Now refresh problem solved.
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.
HiBestiaDemona,
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 🙂 I was not able to get rid of it, so I gave up, as the PC was connected...
...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. 🙂 I hope it will not come back after refresh. Let me restart ...
Johnnmarnell: many thanks for hint
R.
- update: refresh is working also after restart.
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.
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.
Bellz2195I noticed that my issue was happening because the file was showing "Upload Pending".
My solution: I unconnected and re-connected to the network and issue went away.
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.
I got the same error message and behavior.
Any solution for that?
It is causing issues, when running update with scheduler.
thx a lot.
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.
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.
thx Woopakay72
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)
R.
