November 15, 2021
I have created a file that pulls production information from multiple external sources (and the current workbook), this data is manipulated in PQ with all unnecessary information cut out, then several tables are loaded into the Data Model. In the Data Model, I have several measures that calculate multiple things such as: opening stocks, closing stocks, planned production and total deliveries required to match the planned production.
I was then tasked with the idea of having a calendar (which gets loaded into PQ and then PP) where users could mark certain days as 'Blocked out', effectively saying that we could not receive deliveries on these days. What my model does is sum the planned production on these blocked out days and add it to the current planned production for the day immediately before, this would then calculate a new, increased delivery required on the days that aren't blocked out.
There is also another table in the workbook that users can edit that gets loaded into PQ/PP upon refresh that allows the user to manage what product is stored in 1 of 20 or so different locations at any given date (this is so, if we require 100 units of Product A on a day but the currently max holding capacity for Product A is 50, the user can allocate an additional location with a capacity of 50 units to allow for a new max holding capacity of 100 for Product A.
Currently, when the user has made any changes that they wish to see reflected in the dashboard they press a macro that simply refreshes the dashboard table, however, this seems to take several minutes and I can't figure out why.
So my question is: Is it bad practice to have a data model that feeds off user-entered information from the current workbook? And if this shouldn't be a problem, what is the best way to approach this?
July 16, 2010
Whether the user entered information is in the current workbook or an external source wouldn't make any difference to the refresh process. I presume you've tested the time it takes to manually refresh vs using the macro to refresh.
There are some ideas on how to speed up slow Power Query refreshes here.