On further investigation I found that if I individually refreshed the small number of queries that actually loaded data into the workbook, they all refreshed very quickly and with none of the ill-effects seen with a Refresh All. That said, there was clearly something going wrong with the refresh in this case. I’m a big fan of using references to split complex logic up into separate queries, and in this case it was absolutely the right thing to do because otherwise the workbook would have been unmaintainable. To give you an idea of the complexity here’s what the Query Dependencies view looked like: The queries that were being loaded into the workbook were referencing several other queries that in turn referenced several other queries, and indeed there were some reference chains that were over ten queries long. Only a small number of these fifty queries were being loaded into the workbook and none were being loaded into the Excel Data Model. Although all of the data used in the queries was coming from tables in the workbook itself and the data volumes were small, there were fifty Power Query queries and clicking Refresh All resulted in a large, prolonged spike in CPU and memory usage by Excel. Recently I was asked by a customer to do some tuning on an Excel workbook with a lot of Power Query queries in it.
0 kommentar(er)
