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.
Oct 14 2021 10:40 AM
@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.
Oct 22 2021 06:56 AM
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.