Nov 20 2019 06:42 AM
Nov 20 2019 06:42 AM
I have been working with Power Query / Get&Transform for several years now, and have now run into a problem I have never encountered before. What I am trying to do is load a new query to the data model. Normally this is no problem, but the last few days even the simplest of queries (e.g. 5 rows, no edits) get stuck. When I load a query it returns the "xxx rows loaded" message in the Workbook Queries pane, but the loading wheel (the green dots) is still going in a circle. When I check the data model the data is also not visible. So far the loading has never stopped (unless I cancel the query) since I encountered this problem. The size of the query does not matter, I tested it with a very simple query of 5 rows, and this does not load either.
If I change the load to options I can load the query as connection only without any issues. Loading as a table at times also results in the eternal loading issue (even though it does generate some of the data). Thus far I have not encountered the issue while refreshing/editing existing queries.
I've uninstalled/reinstalled office multiple times, even using the office remove tool, and this solves the issue for a short period (sometimes a few days, other times a few hours). I have not been able to discover any kind of consistency in when the problem starts to happen ( e.g. refreshing a specific query or something), but once it happens I can no longer load any new queries. I am using office version Pro Plus 2016.
I've checked all over the web, but have not seen any other posts regarding this eternal loading issue, so I hope one of you can help me figure this out.
Nov 20 2019 10:44 AM - edited Nov 20 2019 10:47 AM
Nov 20 2019 12:03 PM
Stephan, is that only with Power Query, are there any issues if you load data to data model by other way (e.g. through Add table to data model or by PivotTable)?
Nov 21 2019 12:12 AM
Nov 21 2019 12:26 AM
And if you do one more test - run Excel in safe mode (just in case, Win+R->excel /safe) and try to load query in data model. Power Pivot is not available in safe mode, but data model itself exists and Power Query works. You may check the data model after that by re-opening the file in normal mode.
Nov 21 2019 12:34 AM
Nov 21 2019 07:55 AM
If your colleague is on exactly the same version as your are when I have no idea what it could be. If so, not a bug in concrete build, data model itself works, other add-ins don't affect connection with data model.
I'll ask other expert if they have a guess.
Nov 22 2019 07:11 AM
Thanks for putting in the time. Yes, my colleague is on the same version. The only thing I can think of is reinstalling the OS from scratch and hope for the best..
Dec 09 2019 04:16 AM - edited Dec 09 2019 04:16 AM
Were you able to find anything? I am still having the same issue :(.
Dec 09 2019 04:48 AM
Nope, no feedback with what could be the reason. I only see that some other people have the same bug.
Jan 02 2020 11:13 AM
I have the exact same issue. Seems to be a bug on 2016 Pro Plus. There's more people here who have the issue : Technet Microsoft Forum Problem seems to have started with the November update, but there aren't any fixes except manually downgrading.
Jan 06 2020 12:18 AM
@sharky111 Thanks for letting me know! Really had started to think I was the only one that had experienced this. Hopefully there will be a solution soon.
Jan 13 2020 12:52 AM
This is probably one of the worst "solutions" I've ever come up with, but...
If you open the workbook in Excel 2013, go to Workbook Queries (Power Query tab, Show Pane), hover over the query, click Load To... in the popup, then make sure Only Create Connection and Add this data to the Data Model are ticked in the next model, then click Load. Save everything and open en Excel 2016 again.
This has been the only way that I found so far to get around the load problem and actually be able to continue with work. And again, I know this is a really bad workaround.
Apr 16 2020 09:42 AM
This remains an issue. I have been using Get & Transform (Power Query) for several months to update price book data in SFDC, but today I started getting this load issue when trying to merge 2 queries. It simply starts loading millions of rows from an external source (or so the text notification says). Has there been any movement on finding a solution? This has been an issue for over a year I believe.
Apr 20 2020 12:08 AM
Apr 20 2020 10:10 AM
I'm not sure this is the same issue that I am having, but I will work through the suggestions to see if anything works. Thank you for forwarding the info.
Nov 05 2020 08:43 AM
I've had the same issue a couple times this year using Power Query to import data from the SQL server. The first time, I gave up and went to an older saved version of the file and rebuilt the remaining report.
It looks like clearing the cache fixed the problem. I actually cleared all 3 - Data, Q&A, and Folded Artifacts. I'm relatively new to Power BI, so I'm not familiar with the last two.
Thanks for the help!
Nov 05 2020 11:59 AM
In Excel Power Query there is only data cache, thus two questions less. In general yes, keep Excel/Power Query updated and clean caches if you don't use it day-to-day.