How to Run an Excel Model from Power Apps or Power BI Securely
Question details
The user wants to collect user inputs, process them through an Excel-based financial and solar model, and display the results without allowing end-users to see or access the underlying workbook logic.
- Product
- Microsoft Excel, Power BI, Power Apps
- Device & OS
- not provided
- Scenario
- Setting up a secure data collection and reporting pipeline for a proprietary Excel calculation model.
- Observed behavior
- The goal is to separate the data input, the calculation model, and the result presentation layers to protect the proprietary Excel model from being exposed.
Ensure you have an active Microsoft 365 subscription with access to SharePoint or OneDrive, as well as the necessary permissions to create workspaces in Power BI and workflows in Power Automate or Power Apps.
Use SharePoint, Power Query, and Power BI to Secure the Model
Store your main calculation model securely on SharePoint, collect inputs via a separate shared file, and present results using Power BI.
This method prevents users from accessing your proprietary financial and solar models. You separate the architecture into three layers: an input layer (a shared Excel file or Form), a calculation layer (your hidden Excel model using Power Query), and a presentation layer (Power BI).
Set up a new Excel file on SharePoint or OneDrive where users can enter their data. Ensure they have edit permissions only for this specific file.
Open your main secured Excel model. Go to the Data tab, click Get Data > From File > From Workbook, and connect it to the input workbook via Power Query so the model automatically updates with user inputs.
Save this main calculation workbook in a highly restricted SharePoint folder. Do not grant end-users any read or write access to this folder.
Open Power BI Desktop, connect to your main secured Excel model on SharePoint to import the calculated outputs, build your visual dashboard, and publish it to the Power BI Service for your users to view.
Collect Inputs via Power Apps and Automate with Power Automate
Use Power Apps for a clean user interface to gather data and Power Automate to pass it to your hidden Excel model.
Need a Lightweight Tool for Building Complex Financial Models?
While automated cloud integrations with Power Apps require the Microsoft ecosystem, WPS Spreadsheet is an excellent, free alternative for building, testing, and securing your complex financial models locally before enterprise deployment. It offers high compatibility with standard spreadsheet formats and a familiar interface.
- 1. Download and install WPS Office: Visit the official WPS website and download the free version for your operating system.
- 2. Open your model: Launch WPS Spreadsheet and open your existing .xlsx financial or solar models.
- 3. Edit and protect: Use the Review tab to protect sheets and lock calculation cells, ensuring your local models remain secure while you test them.
Frequently Asked Questions
Can Power BI be used to collect user inputs directly?
No, Power BI is fundamentally designed for data visualization and presentation, not data entry. To collect user inputs, you should utilize tools like Power Apps, Microsoft Forms, or a shared Excel file hosted on SharePoint.
How do I keep my Excel formulas hidden from users?
Store your main Excel calculation model in a restricted SharePoint or OneDrive folder where end-users have no access. Use Power Query to pull data from a separate input file, and only display the final calculations in a Power BI dashboard.
What is Power Query and how does it help in this scenario?
Power Query is a data connectivity technology. In this workflow, it allows your hidden Excel model to securely pull and transform input data from a public shared file without exposing the main workbook's proprietary logic to the end users.
Can I use Power Automate with Excel files stored on my local desktop?
To seamlessly use Power Automate and Power Apps, your Excel file should be hosted in a cloud environment like SharePoint Online or OneDrive for Business. This ensures the automated flows can access and update the data in real-time.
