![]() the good news is that I improved performance when opening the file and query editor from scratch. CSV sources and then updating my queries to read them. To speed up performance of my small example, I tried converting all the Excel tables to external. I could move my source (Excel) data back out to external XLS files, but I would think that this would only slow things down further. Is there any workaround to this issue? My source data is in the spreadsheet where I have my PQ model. I've done that, but it doesn't seem to help much. Another site said to enable the Fast Data Load under Query Options. I've looked online and one site said that PQ updates ALL the queries when you do a refresh, so the more queries, the longer it takes. So, although 1 minute is not a long time to wait, it feels like it especially when I'm making a small change to the model and waiting to update a pivot table to ensure I've made that change correctly. ![]() Since I'm building the model, I'm tinkering with it, and constantly refreshing it. My hypothesis is that it's not the data sources that are slowing things down, but the 30 or so queries I've built. The refreshes sometimes take about 30 secs to 1 minute, although not all the time. In particular, this occurs when I refresh any 1 of the 5 pivot tables I've built from the model, and also when I relaunch Power Query and click on any of the queries to view its data. Somewhere along the way in my model building, I've observed very slow Excel performance on the refresh. It's not that I've built the queries any more granular than necessary, but it's the beginning of a very complex financial model, query-wise, although not math-wise. In my model so far, I've got about 30 queries. I also added 2 other Excel tables, each with 3,000 rows of data in them (about 3 columns wide). The raw, test data has about 4 Excel tables, with anywhere from 3 to 30 rows of data in it. To start small, I'm using some test data. I've had a chance to start to build out a costing model with a lot of data manipulations (e.g. Excel for Decision Making Under Uncertainty Course. ![]() Excel for Customer Service Professionals. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |