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

%3CLINGO-SUB%20id%3D%22lingo-sub-1691715%22%20slang%3D%22en-US%22%3EExcel%202016%20-%20Power%20Query%20Error%3A%20%22Cannot%20Refresh%20Data%20Type%22%20but%20data%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1691715%22%20slang%3D%22en-US%22%3E%3CP%3ESuddenly%20one%20day%20last%20week%20when%20I%20refresh%20my%20Excel%202016%20Power%20Query%20file%20I%20receive%20the%20error%20%22Cannot%20refresh%20data%20type.%22%26nbsp%3B%20However%2C%20I%20click%20OK%20and%20data%20still%20refreshes%20but%20I%20do%20not%20understand%20how%20to%20fix%20the%20file%20(if%20needed)%20so%20the%20error%20does%20not%20show.%26nbsp%3B%20Nothing%20has%20changed%20in%20the%20query--cannot%20find%20anything%20on%20Google%20with%20this%20same%20error.%26nbsp%3B%20Anyone%20have%20an%20idea%20why%20this%20suddenly%20started%20occurring%20and%2For%20how%20to%20fix%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1691715%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1747966%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20-%20Power%20Query%20Error%3A%20%22Cannot%20Refresh%20Data%20Type%22%20but%20data%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1747966%22%20slang%3D%22en-US%22%3E%3CP%3EI%20got%20the%20same%20error%20message%20and%20behavior.%3C%2FP%3E%3CP%3EAny%20solution%20for%20that%3F%3C%2FP%3E%3CP%3EIt%20is%20causing%20issues%2C%20when%20running%20update%20with%20scheduler.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1748993%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20-%20Power%20Query%20Error%3A%20%22Cannot%20Refresh%20Data%20Type%22%20but%20data%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1748993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F277431%22%20target%3D%22_blank%22%3E%40robertoz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20that%20I%20have%20found%20and%20no%20one%20else%20has%20replied%20to%20this%20post%20for%20a%20possible%20solution.%26nbsp%3B%20My%20Power%20Query%20also%20runs%20on%20Task%20Scheduler%20but%20does%20not%20seem%20to%20interfere%20with%20the%20data%20refresh.%26nbsp%3B%20If%2Fwhen%20I%20hear%20of%20or%20find%20a%20resolution%2C%20I%20will%20post%20it%20here.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749152%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20-%20Power%20Query%20Error%3A%20%22Cannot%20Refresh%20Data%20Type%22%20but%20data%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749152%22%20slang%3D%22en-US%22%3E%3CP%3Ethx%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F801479%22%20target%3D%22_blank%22%3E%40Woopakay72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebtw.%20it%20is%20happening%20on%20all%20my%20excel%20files%20when%20using%20Refresh%20All%20icon.%3C%2FP%3E%3CP%3EHowever%20I%20have%20just%20realized%3A%3C%2FP%3E%3CP%3E1.%20when%20I%20start%20Refresh%20All%20for%20the%20same%20file%2C%20just%20from%20another%20computer%2C%20it%20runs%20without%20issues%20%3D%26gt%3B%20means%20the%20issue%20seems%20not%20to%20be%20related%20to%20the%20specific%20file%2C%20rather%20to%20the%20computer%20or%20its%20installation%3C%2FP%3E%3CP%3E2.%20when%20I%20start%20refresh%20query%20by%20right%20click%20menu%20one-by-one%20query%20only%2C%20it%20runs%20without%20this%20message%20%3D%26gt%3B%20means%20the%20issue%20might%20be%20somehow%20connected%20to%20Refresh%20All%20functionality...%3C%2FP%3E%3CP%3EOne%20note%20here%3A%20computer%2C%20where%20the%20issue%20appears%2C%20I%20am%20accessing%20via%20RemoteDesktop.%20But%20the%20other%20computer%20I%20tried%20and%20worked%20well%2C%20I%20am%20logged%20in%20directly%2Flocally.%20Both%20computer%20have%20exact%20the%20same%20Version%20of%20MS%20Office%3A%20Version%202009%20(Build%2013231.20262)%3C%2FP%3E%3CP%3ER.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1749381%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%202016%20-%20Power%20Query%20Error%3A%20%22Cannot%20Refresh%20Data%20Type%22%20but%20data%20refreshes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1749381%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F277431%22%20target%3D%22_blank%22%3E%40robertoz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%26nbsp%3B%20Yes%2C%20it%20happens%20on%20all%20the%20files%20with%20PowerQuery%20whether%20it%20is%20through%20Task%20Scheduler%20to%20run%20the%26nbsp%3B%20PowerQuery%20or%20manually%20clicking%20%22Refresh%20All.%22%26nbsp%3B%20My%20back-up%20has%20the%20same%20issue%20with%20his%20copies%20of%20the%20same%20files.%26nbsp%3B%20My%20reports%20have%20been%20running%20without%20incident%20for%20nearly%20four%20years%20so%20something%20has%20changed.%26nbsp%3B%20In%20my%20case%20I%20don't%20think%20it%20is%20the%20computer.%26nbsp%3B%20I%2C%20too%2C%20am%20working%20remotely%20but%20have%20been%20since%20March--this%20is%20a%20new%20issue.%3CBR%20%2F%3E2.%26nbsp%3B%20I%20have%20not%20tried%20refreshing%20each%20Query....there%20are%20lot%20of%20them...interesting%20test%20though.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EWe%20are%20on%20Windows%2010%20with%20the%20same%20issue.%26nbsp%3B%20I%20am%20wondering%20what%20Data%20Type%20changed%20that%20it%20cannot%20refresh....or%20as%20you%20said%20might%20be%20an%20issue%20with%20Refresh%20All.%26nbsp%3B%20I%20wish%20MicroSoft%20would%20chime%20in%20on%20this!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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?

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