For a project I’m doing, I am stuck on a solution which doesn’t seem ideal. The problem is the following:

I have a fully functional and COMPLEX Excel workbook where you can enter input data in an ‘input’ sheet, next there is a ‘back-end’ sheet where the input data is undergoing some calculations based on a filter you use (INDEX MATCH functions etc. based on data you choose in a filter on the graph sheet). So if you would change the filter to another value, the data from ‘back-end’ will also change. And finally there is a ‘graph’ sheet where some graphs are displayed which are based on data from ‘back-end’. The graphs change based on a filter where you can pick a value.

Now we want to bring some functionalities to POWER BI. This means: 1) the filter 2) the graphs. But the data from the back-end sheet would take too much time te recreate in Power Bi. That’s why we think it’s best to keep that in our Excel workbook and connect to it with Power Bi. The Excel would just be the data input and we wouldn’t want to open this. It is stored in a Sharepoint folder.

currently we made a POWER AUTOMATE flow that gets triggered when you are in Power Bi, you select a value from a filter/slicer (same as in Excel workbook), and you click on ‘Run flow’. Next, in the flow, we do a ‘update row in Excel’ action. It takes the value it gets from Power BI, and updates the filter value in the Excel filter in the ‘graphs’ sheet. Since the value changed, the data in ‘back-end’ also changes. In the Power automate flow, I have a delay action of 3minutes so the data can update and an autosave can take place. Finally I do a ‘refresh dataset’ action in Power automate. Finally, I need to do a ‘refresh visuals’ in my Power Bi service dashboard so my graphs in the dashboard also refresh based on the new data.

This whole process takes approximately 5m which is not acceptable. Also the ‘update row in Excel’ action in Power Automate sometimes takes much longer than normal (Gateway timeout).

Can we make our current setup faster? Or is there another way we can approach this?

All help is welcome.

Kind regards,
Joran