Get&Transform / Power Query stuck on load to Data Model

Copper Contributor

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
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 you

@SMiddel 

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. 

 

@Sergei Baklan : I can add (pivot) tables to the data model without any problem. 

@SMiddel 

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. 

@Sergei Baklan
The query gets stuck on load even in safe mode.

I also sent one of the stuck workbooks to a colleague, and he can load the query without issues.

@SMiddel 

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.

@Sergei Baklan 

 

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

@Sergei Baklan

 

Were you able to find anything? I am still having the same issue :(.

@SMiddel 

Nope, no feedback with what could be the reason. I only see that some other people have the same bug.

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.

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

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.

@SMiddel 

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 Technet forum 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 

@Abiola1 

 

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!

@Greg_C4450 

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.