Power Query not loading data - Stalling

[troyhimes]

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

[Anonymous]

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]

@Anonymous 

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.  

[Anonymous]

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