Hello,
I have created a data model within a workbook comprised of 15+ Excel data sources and 1-2 from SharePoint. The Excel data source files range from 10k-100k rows, which are located on our network drive. I use Get & Transform to pull in each of those files to clean the data. Some queries have very little applied steps, and some have 10+ steps. Some steps are minor, such as removing columns; however, some are more complex, such as conditional calculations. Ultimately, the workbook uses the data model to populate the front-end of the report consisting of 20+ pivot tables/charts. A few slicers control the tables/charts on the front-end. There is also VBA within the workbook, but only to format and print.
There are two issues I wrestle with every time I introduce a new data source via Get & Transform:
- The "Refresh All" takes about 2-5 minutes to perform. Is this normal?
- The file size is only 8.5 mb; however saving the file takes over 3 minutes and opening the file takes a long time as well. (When opening the prompt freezes at 0%) What is causing this lock up?
