“I heard a lot about Power Automate and it looks promising, but how do I even start building a Power Automate based solution?”
Power Automate is a great tool for many users, even those who don’t have much experience with programming. It’s widely available, free to use, and has a user friendly drag and drop interface to build the flows. But often it’s not enough to “just build a flow” and be done with it. The flow is frequently part of something bigger, one piece of a whole solution to solve a specific problem.
What are the other pieces? How do you think about building a Power Automate based solution?
Data source
When building a Power Platform solution, the first thing to consider are the data sources – what will the flow interact with? Is it a flow that uses only data that’s already available in other (mainly M365) applications, e.g. Outlook? Data it’ll only read and process within the flow? If so, you’re done with data sources.
However, many flows will need more than what’s already available. They might need some user input, some intermediate data storage, some place where you’ll not only read, but edit and create new data too. From Power Automate perspective you can consider 3 such data sources.
Excel
The data source most people will be familiar with, yet at the same time the worst option. You can interact with Excel data in Power Automate flows, but it’s not very intuitive – it can be slow, return data in a strange format, sometimes the files get locked, and it has too many limitations in general.
It’s an acceptable source to read data from or to export data to, but I’d avoid using it as the main data source for a solution.
SharePoint
From my experience that’s what most Power Automate flows use as a data source, probably because it’s included in the free license. But it has more benefits than that. SharePoint list / library is a single source of truth (unlike Excel files that always exist in multiple versions after some time), you can manage permissions on the item / file level, it’s easy to work with using the standard flow actions, everything is versioned, and it already has a user interface. And it has a great search!
SharePoint lists can handle millions of items and SharePoint libraries are the preferred solution even if you use Dataverse for the data. Where it comes short are relationship between lists – you can have a lookup column, but that’s about it, and if you need more granular permissions.
But in general, SharePoint is a totally fine data storage for many Power Platform solutions, including the Approval process template. And from one perspective even better than Dataverse – it’s not limited to a single environment.
Dataverse
If SharePoint lists are not good enough, if you have a ton of data with many relationships, or if you need more granular permissions, maybe even on a column level, Dataverse is the way to go. While SharePoint can be used as a database, Dataverse was designed to be used as a database.
The downside is that you can’t use Dataverse without premium licenses. You’ll also have to build the user interface to interact with the data, rebuild everything you already have in SharePoint with Power Apps.
But if you’re building serious, complex solutions you should approach them as any other development project – you should use a proper database. From the Power Platform perspective it means Dataverse.
User interface
Once you decide what data sources you’ll use, it’s time to think about user interaction. How will user access the data? What interactions are expected?
Microsoft Forms
If it’s only about collecting data, you can go as simple as using Microsoft Forms. A simple form users fill out and that’s it. While there’s a limited customisability when using MS Forms, it has also one huge advantage – Forms can be shared outside of your organisation! For easy data collection it could be the way to go.
SharePoint
But even if you use Forms, you might want to store, read, and further process the data. That’s when SharePoint interface might come into play.
The huge benefit of SharePoint is that it already has a user interface. You can create, edit, view, delete and search directly on the SharePoint site in the lists and libraries. There’s no need to develop anything over data stored in SharePoint, it has it all covered. If you’re happy with having the same form for all the users, SharePoint might be the way to go.
But if you need something more, create a different form for different users, guide users through the process by some logic on the form, you’ll have to do some development.
Power Apps
To add some additional logic to the forms or to build complex apps, Power Apps is the way to go. You can build a few types of apps, for this article let’s consider those available without premium license (= no model driven apps).
The first type is a replacement for the standard SharePoint forms. If you’re happy with using SharePoint, but feel limited by the SharePoint forms, you can replace them with Power Apps app. Instead of the standard new, view, and edit form you can have a Power Apps application with additional logic. It can do everything as a Canvas app while benefiting from the search and list / library views in SharePoint.
The second type is a standalone Canvas app, where you hide the data source on the background behind an application. Users won’t even know they’re working with Excel / SharePoint / Dataverse, from their perspective it’ll be a designated app built specifically for them. It’ll have it’s own url, can be embedded into Teams, opened on mobile devices, it can follow a proper ALM, it’s a full scale application. It’s also the only way how to work with data in Dataverse as there’s no UI – everything must be done through an app.
Power Automate flow
Where you’re running out of options is the logic on the background, the implementation of the whole process in Power Automate. Now you know what data source it’ll use and how users will interact with the tool, building a flow is how you put it all together.
It doesn’t really matter which combination of data source you choose, a flow can work with all of them.
Summary
Before you even start building a flow, there’re two questions you should answer. Where will you store the data, and how will users interact with it? As you can see in this article, there’re multiple pieces to choose from when building a Power Automate based solution.
Firstly, you have to select the data sources it’ll use – is it a VERY simple personal solution using Excel, a team solution built on SharePoint, or a full scale application using Dataverse? Selection of the data source has then an impact on the user interface too.
Secondly, the user interface. Is it just collecting some data internally or externally? Microsoft Forms is good enough. Will users need a way to interact with the data? If you’re storing data in SharePoint the standard forms can be all you need. But if you need something more, some logic on the form, or if it should behave as a full scale application, you’ll need a Power Apps app.
Once you decide on these two points, you can move forward with the flow. A process that will bind it all together, the data source, user interface, and the logic of the whole process.