Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Modeller_88
New Member

Using Excel as a 'back end' with Power Apps & PowerBI

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 

1 REPLY 1
lbendlin
Super User
Super User

3. Hosted the Excel on OneDrive

Data sources on OneDrive/Sharepoint do not require a gateway and when they change they "usually refresh the dataset within the hour" (as per Microsoft).  If that's not good enough for you you can add these data sources to the gateway (warning:irreversible)  and then use API refresh (for example) to speed up the process/make it more  predictable.

 

I sincerely wish that you can move on from that frankensolution to  a proper setup posthaste.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.