SharePoint has always been a great tool for quickly solving business problems. Microsoft’s “Power Platform” is a potent solution that can enable business processes at a speed that’s hard to match and without hard-to-mitigate limitations. It’s a set of tools built on top of Office 365 that enables rapid creation of apps, data visualization and process automation, facilitates a whole new class of solutions, all with minimal investment and time required. In fact, they might be able to free your organization from costly third-party workflow and form tool licenses.
The Power Platform is a collection of three Office 365 software products: PowerBI, PowerApps and Flow. It enables businesses to perform three key actions: Analyze, Act and Automate.
- PowerBI, a self-service Business Intelligence reporting tool, allows business to analyze their data, using built-in integrations and easy dashboard building tools.
- PowerApps, a code-free application development tool, allows business to act on their data by creating forms to manipulate it.
- Flow, tragically breaking the “Power-“ prefix chain, is an automation tool that enables automation on a scale entirely new to the Office 365 world. It’s my favorite of the tools here simply because of how much it continues to grow.
Together, these tools enable the “Triple-A Loop” – Analyze, Act and Automate. Microsoft is continuing to develop the Power Platform as well – they recently announced new “AI Builder” capabilities that can help harness the power of AI within this loop.
I’ve created a hypothetical example, based on our recent client work, to show a simple example of the Triple-A loop using the Power Platform. Let’s walk through the scenario and see how we can put the platform to work!
Scenario: Data intake and reporting
For an important business process, the client has been tracking some of their key metrics in an Excel spreadsheet. As the number of contributors has grown, this is proving to be unwieldly – users struggle to enter data and the business can’t report on the data easily. The client is looking for a simple solution that addresses their pain points.
- Users need to be able to submit data easily
- Client needs to be able to import their legacy data into the system
- Client wants to be able to see rich dashboards visualizing their data (and other existing business data that ties into it)
Since the Client already has Office 365 and SharePoint Online, we can use the Power Platform to tackle each of these requirements with ease – maybe not surprising given the theme of this blog! Let’s take a look at how we can get this done.
Submitting data easily with PowerApps
PowerApps is essentially the successor to the legacy product InfoPath, which was used for years to create custom forms. Although PowerApps can’t quite do everything InfoPath did, from my testing I’d say almost all classic InfoPath forms can be migrated to PowerApps – and they’re going to look a lot better and be more user-friendly, as well. Microsoft also provides guidance on how to achieve a lot of the common InfoPath requirements in PowerApps.
In PowerApps, with just a few clicks, you can create a default app from a SharePoint list – for more complicated situations you’ll want to roll your own, but it’s a great way to get started.
We’ll need a way to store data, of course, and in roughly five minutes, I set up the following SharePoint list as a proof-of-concept:
Then, I created a PowerApp that allows easy data submission – I simplified the initial screen to just show a button to add data, since we want that to be the main focus of this piece. Here it is published to the SharePoint site:
Clicking the button takes the user to this form:
Simple – and user friendly! Thanks to PowerApps’ mobile compatibility, we can even use this form on mobile devices. For now, I’ve dropped it on the home page of my SharePoint test site for quick access.
Importing legacy data using Microsoft Flow
The client also wants to import batches of legacy data into the system. There’s a lot of ways out there to do this, some higher-powered than others, but one fairly potent way is to use a Microsoft Flow. With Flow, we can construct a “flow” – think, “workflow” – that can read from an Excel file, loop through each row, and add it to SharePoint. We can even add logic to avoid entering duplicates, if needed.
We’ll start with this out-of-the-box Flow template:
I first created and uploaded this file as DataImport.xlsx:
With the file created, I set up the following flow:
Running this added the following data to the SharePoint list:
Now we have an easy way for the business to import legacy data!
Visualizing your data with PowerBI
With data in the system, we can start creating visualizations. Using PowerBi Desktop, we can easily connect the SharePoint list to PowerBI as a data source, allowing us to use all of PowerBI’s visualization capabilities with our data, along with any external data we might have to help us enrich it.
The only ‘trick’ to the process is that you’ll need to go into the query in PowerBI and set columns to the data types you expect – for instance, setting numeric columns to “Whole Number” if appropriate. You can do even more with the data in PowerBI if you’d like, including setting up advanced calculated columns and measures.
Then, we can put this PowerBI report right on our SharePoint site.
The finished product
With all these pieces together, we have a solution for the original requirements. Users can enter data, we can import legacy data, and we can view reports on the data – all through the Power Platform and Office 365.
What I like most about this is that each part is modular – there’s no part of this solution that’s going to tie down the rest of it, which makes it a great option for even a short-term fix.
Microsoft’s Power Platform is a potent solution that can enable business processes at a speed that’s hard to match and without hard-to-mitigate limitations. Are you interested in starting your journey to modernizing your organization’s digital workplace using Microsoft’s Power Platform? We can help! Check out our Digital Workplace Solution Brief or contact us to learn more.