Your reporting environment should not be static; it should continue growing and evolving. Those changes should come under one of three categories. Whenever you look at the changes coming to you, classify them as Comply, Sustain or Improve. You must make compliance changes, which must be the highest priority. Next comes Sustainability, which is about ensuring that what you have now keeps working. That should be simple, but as your model evolves, you must consider and ensure those new improvements or compliance changes do not break anything. Lastly, you have Improvements; these must always be your lowest priority, although these will often get the most attention.
In this series, we updated the initial report pack from another video series, looking at the challenges of evolving and improving a report pack while also considering what Copilot could bring.
Jesmond Dene parkrun was only the beginning. Newcastle has more than one parkrun, so the next logical step is to adapt the report architecture to pull every event through. This rescaling of report context happens at every organisation that starts to use Power BI.
Setting up expanded solutions is a matter of understanding how your data combines. This is never as simple as it sounds, and no one-size-fits-all solution exists. In this case, each occurrence of an event generates two spreadsheets of data. These need to be combined into a data model. We have built a Data Flow for each event with a single parameter to define the event, so each event serves up four tables. Those four tables are then appended together to build the new data model.
In our example, we combined the tables and reused nearly all previously written measures. There is no legitimate reason not to be able to expand this solution to cover a significantly larger number of events. At a certain point, however, it would be better to move the solution into Fabric, with a different ETL processing suite, as dataflow do not support incremental refreshes, so each event is effectively rebuilt each week on refresh.
In a perfect world, an event monitor can also identify and refresh the data when the event data is updated. At present, the refresh only happens when it is triggered.
The Power Platform is all about understanding your data. When you understand your data and how it interacts with itself, you can expand it. In this example we have progressed from a single event happening on one day to all parkruns at a single event, before finally updating it to include all parkruns across multiple events.
The lesson to learn here is that your business can also benefit from these principles. The results from one day could be the single day sales reports for one of your stores, for which you currently have someone producing a management summary daily. Power Platform allows those existing daily reports to be combined into a new data model, suddenly bringing trends to life and giving much richer content than imagined. Finally, with a small amount of extra effort, it is possible to bring all your store data to life.
REMEMBER: This whole solution is based on a “Daily Spreadsheet”.
