Multi-user PoweApps with shared data source

[ralina]

Hello everyone,

Currently I have this Excel document that has several tables that contain calculated fields and VBA macros. This file is shared on One Drive/Share point and at times, if multiple people edit it at the same time, this results in conflict errors when the macros are triggered.

My goal is to re-create the logic of this Excel file and have the data accessible to multiple users at the same time while providing also nicer user interface, using PowerApps. How is this possible and what combination of Microsoft tools should I consider for fast and reliable, multi-user supported solution, compatible with PowerApps- Common Data Service, PowerQuery, Azure Table Service, SQL Server, etc.?

Any ideas?

[WarrenBelz · Accepted solution]

Hi @ralina ,

Firstly, it depends on whether you want to use (and only pay for) the standard E3 licence, in which case SharePoint does the job generally adequately. We run a substantial operation solely on this platform.

If you want to pay for per-user licences (and get premium connectors), then Azure SQL is probably the best option.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Anonymous · Accepted solution]

@ralina 

This question really depends on the complexity of your Excel file.

For example, if your VBA is reshaping your data, building reports, building printable docs, etc then PowerApps + SharePoint (as @WarrenBelz points out) will be able to do most of those things for you but you may also need to leverage other solutions as well eg Power BI for reports, Power Automate + a Premium Word connector to build docs (as one option), etc.

I currently have a couple of key shared Excel files that leverage PowerQuery for data shaping. Most of this data shaping couldn't be handled by PowerApps + SharePoint - PowerApps functions just don't have the same power (pun intended 🙂 ) as PowerQuery ... so I use Power BI for reporting to management. This comes with it's own set of challenges though, which I'm working through now.

If you decide to use one of CDS/SQL/Azure then your use costs increase again but these do offer database functionality, which maybe what you need for your solution.

There's no magic bullet here and the final solution will probably come down to cost once you lay out all the options you need.

[ralina]

Great explanation! Thank you!