rogersp188
New member
- Joined
- Mar 3, 2019
- Messages
- 6
- Reaction score
- 0
- Points
- 0
- Location
- Denver, CO
- Excel Version(s)
- 2016 x64
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 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.
