cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
troyhimes Helper I
Helper I

Power Query not loading data - Stalling

Recently I have had both relatively small power queries (180 rows, 10 lines of M code) and larger (approx. 6000 rows, 25 lines of M code) stall at the load stage. It happened first with the larger query and I thought it had to do with the size of the query. But it has now happened with smaller queries as well. 

If it is loaded to a sheet, it will load a small portion of the query (15% of the query output rows, the last row consists of 3 periods in each column and then stall without loading anymore data. The Workbook Queries status bar shows that the query is still loading, but never goes anywhere.

The same result if I load it to the data model without anything at all showing up in the data model.

Any ideas?

Thanks

3 REPLIES 3
Super User I
Super User I

Re: Power Query not loading data - Stalling

Hi @troyhimes ,

 

Would be nice to know what connector is used (i.e. a source of data).

Your existing code also would be good 😁.

 

From my experience, it can be caused by several things. I saw a badly written code that was loading over 5Gb from a 100Mb source file before crashing. I guess, in your case, there may be something like this. Did you try to leave only the export steps (usually Source + 1-2 steps to format source data)? How long does this take to load? If it fails at this step - there is something wrong with the connector. If it is fast, but the rest of the code takes a long time to load, most likely, it is a bad use of grouping or joining.

 

Kind regards,

JB

 

troyhimes Helper I
Helper I

Re: Power Query not loading data - Stalling

@jborro 

I'm not writing any of my own code, it's all auto generated, but here it is.  I posted a link to the actual spreadsheet on another forum and several members were able to load the data without any issues.   This is leading me to believe that it may be just an odd MS issue.

 

I again had the same issue crop up in another query yesterday and after reading your comment deleted half of the code and it loaded fine.  Pasted the second half of  the code back in and again it loaded just fine.  

 

2019-11-18_17-55-11.png

Super User I
Super User I

Re: Power Query not loading data - Stalling

Hi @troyhimes 

 

you are probably right. I can't see anything suspicious in the code, so it may be some specific bug in the connector/environment.

Try also switching off automatic detection of the column types in the project options. This can make it a bit faster, but this should not make the difference between quick and very slow.

 

Kind regards,

JB

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors