Forum Discussion
Get&Transform / Power Query stuck on load to Data Model
Hi all,
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.
Kind regards,
Stephan
17 Replies
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.
Hi JPadrino
For me the issue i described was fixed at the beginning of February with Version 1912 (Build 12325.20344). Please see https://social.technet.microsoft.com/Forums/en-US/aaf4823f-954d-400b-bde0-858639a475f4/power-query-load-to-data-model-constantly-stuckhanging?forum=powerquery&prof=required for more details. Not sure if your issue is the same as mine to be honest.
Kind Regards,
Stephan
Hi Stephan,
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.
Jim
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.
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)?
Thank you both for your replies.
Abiola1 : Unfortunately clearing the cache / restoring defaults has not solved the issue.
SergeiBaklan : I can add (pivot) tables to the data model without any problem.
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.
- Hello,
Let's try this steps and hopefully, it works
1. Click on Get Data dropdown
2. Select Query Options
3. In the Data Load pane of the Query Options dialogue box, Clear Catch and Restore Defaults
4. Click OK.
Then try to load the data to see how things goes..
I have attached a caption of my Power Query
Looking forward to hear from youI'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!
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.
