Excel 2016 - Power Query Error: "Cannot Refresh Data Type" but data refreshes

Copper Contributor

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 got the same error message and behavior.

Any solution for that?

It is causing issues, when running update with scheduler.

 

thx a lot.

@robertoz 

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.

@robertoz 

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!

 

 

@robertoz 

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.):

  • Any columns of data that cannot refresh the data type will be blank.  This is only a problem if those happen to be the columns of data you need for your report.
  • "Cannot refresh data type" does not keep the file from refreshing.
  • I found in one of my sub-queries five columns with a question mark at the top--they had no data type--good news is they don't affect the data I pull into my report.  I added a data type but unfortunately did not fix the issue.  I must have other columns where there is no data type but haven't checked yet.  So is it possible that MicroSoft removed a data type and that's why this error is occuring I wonder.  My report is over 4 years old and never touched the data types.

Hope this helps!

@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.   

@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. 

@Johnnmarnell 

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.  

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. 

Hi@BestiaDemona,

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.

@Woopakay72 

 

The solution of this is simple go to Excel options, Data, Enable data analysis add ins: power pivot.

 

Now refresh problem solved.

@Johnnmarnell 

 

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.

@Woopakay72 

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.