This may be something which has been done before, but I wasn't able to find any explicit resources on it being done, so thought I'd ask.
My organisation has a large number of models which have been built over time in Excel. I'm trying to make these models into a 'back end' with the front end sitting entirely in PowerBI - I can go into the reasons behind doing this if needed, but for now, let's just assume it's something we need to do 🙂
As a 'Proof of Concept' I've taken a very simple Excel model (for calculating amortisation, interest, cashflows, and outstanding balances on a mortgage over time), I've then done the following:
1. Changed model inputs to an Excel table
2. Changed model outputs into an Excel table (using formulas as lookups as required)
3. Hosted the Excel on OneDrive
4. Set up a Power App which allows me to edit the inputs
5. Embedded that PowerApp within PowerBI
6. Done some simple visualisations based off the outputs table
Surprisingly, this *kind of* works. Unsurprisingly, this is rather clunky. the issue is that whilst the Power App works very quickly, and updates the inputs table more or less instantly, the back end and reporting part are not so speedy:
1. Once I update the values in the inputs table via the power app and refresh the charts in the PBI -> the chart often goes to blank
2. If I go into the excel file, all of the calculated elements are blank, the calculations are still there, but no values are showing, probably what causes (1).
3. Once I click on any of them, the whole sheet updates and shows the relevant values.
4. Even once doing this, it generally takes 5/10 minutes for a refresh to actually update my chart to the new values held in the table, even if I can go into the Excel and see that the table values have been updated.
My questions are therefore:
1. Has anyone tried/figured out a better solution to doing this? (I know the back end is ideally not in Excel, for a large, large number of reasons, but we have a significant amount of IP, so re-building all of the is not viable).
2. If not -> Am I correct in thinking that it's a calculation issue which is making the Excel blank in (2) above? If so, there a way to force-recalculate the Excel from the PowerApp? I'd have thought this is doable given it's possible in VBA (and therefore an API which does it exists), but I'm aware that the whole Power Suite works differently?
3. Finally -> any thoughts on why the charts are not updating on refresh, even though the underlying Excel I'm pulling the tables from is? I am assuming this is a sync/lag issue with OneDrive, but I'd happily take any suggestions.
Fingers crossed, I'm assuming most of you are *far* better at this than I am, so there'll be some answers here 🙂
Thanks
