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

[Woopakay72]

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?

[#2]

I got the same error message and behavior.

Any solution for that?

It is causing issues, when running update with scheduler.

thx a lot.

[#3]

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

[#4]

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.

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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. 

[#11]

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.

[#12]

update: refresh is working also after restart.

[#13]

@Woopakay72 

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

Now refresh problem solved.

[#14]

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