Working Queries always load upon opening file as Exclamations

All,

I won't be posting any code for this as this is a behavior of Excel that appears to happen with queries regardless of whether they are operational or not.

So the brief summary is I have a workbook in Excel where I've built quite a complex model utilizing SharePoint Libraries to navigate into excel files as well as pulling CSV's locally and on our regional share drives. I've built these queries to rely upon each other as opposed to every query re-establishing a direct connection to a CSV. As I understood this was required to avoid Firewall Query issues. All good up to this point. My model is a bit sluggish and Excel 2016 doesn't handle the caching as I would have liked. So I'm reworking this whole model in PowerBI to see if the performance gains are worth the learning curve.

What I find immensely frustrating is that when ever I reopen my Excel file I find that a lot of my queries have a yellow exclamation mark and nearly always it is a result of the "Evaluation Cancelled" error. So I just hit Retry, walk away go fill my coffee and come back to repeat the process for my next query. This is the case as I am still developing this model and I of course need to re-establish where I last left off,etc. However with this very iterative and time consuming process its very frustrating. I've played with Table.Buffer at my source files direct connection queries and I've loaded Fast Data Load on my "heavy lifting and transformative" queries. However I've yet to find a solution to getting up and running in the morning faster.

Any best practice suggestions? P.s. Used to use Ken's dynamic parameter table fnGetParameter all over the place to pull my references from an in workbook data table but after learning how sluggish that is over an external excel file's data table or a CSV, I have translated all of my parameters from within the workbook to an external CSV as well. (Which turned out to be necessary to build the model in PowerBI). Random thought there.

I personally have never seen an issue like that in my work, but random thoughts (a) Why would you refresh the queries one by one instead of using data ... refresh all .. refresh all.. ? (b) When doing development I like to wrap the source file call in a Table.FirstN( Source,1000) or somesuch to limit load times. Works wonders and remove when development is complete.

horseyride said:

I personally have never seen an issue like that in my work, but random thoughts (a) Why would you refresh the queries one by one instead of using data ... refresh all .. refresh all.. ? (b) When doing development I like to wrap the source file call in a Table.FirstN( Source,1000) or somesuch to limit load times. Works wonders and remove when development is complete.

Click to expand...

The problem with that is that you will not test the exceptions that may be hidden in the records beyond 1000. The 'proper' way is to create a test bed with all possible outriders. It's a valid first test, but you still have to perform a full test.

horseyride said:

(a) Why would you refresh the queries one by one instead of using data ... refresh all .. refresh all.. ?

Click to expand...

I certainly have attempted this however it seems to just have me sitting for a while at my desk whereas when I do the one offs somehow it actually does end up being a faster process.

Ultimately the question is more, is this standard operating procedure? Why do some of my queries when saving the file and reopening have exclamation marks and some dont?

Literally have two text files on the same share drive with effectively the same steps and connectors and yet often times one is loaded and one isn't.

Could Table.Buffer () be causing issues around this?

Crap... I think this explains it all...

https://social.technet.microsoft.co...rror-evaluation-was-canceled?forum=powerquery

Do you have background analysis disabled? If so, these errors can happen but should be purely cosmetic. The reason they happen is that the editor side pane depends on analysis results. If you mouse over one, this starts an on-demand analysis evaluation. If you then mouse over another one, the previously started on-demand evaluation will be canceled and show the error triangle.
Let me know if the errors interfere with the loading of your data to the workbook or data model. That would be a bug.
Ehren

Click to expand...

https://community.powerbi.com/t5/Desktop/Unexpected-error-Evaluation-was-canceled/td-p/456055/page/2