By Matthew Jackson
Business Intelligence Consultant at Stellar
Alongside our move to more flexible working from home policy, I was tasked to come up with a solution that would allow staff to centrally view a dashboard where members of the team will be working. I came across several candidate tools that could accomplish this. Eventually, I decided on using tools within the Microsoft Power Platform, namely Power Automate, Microsoft Forms and Power BI.
Microsoft Forms
Microsoft Forms was to serve as my front-end input, and a link to the form would be provided to all staff to be filled out once per week for the following week. Unfortunately, Forms was unable to do things like dynamically capture the users’ names based on their login credentials and do automatic date selection for the following Monday. There is also a pro version of Forms that may provide these features. However, the free version worked out in the end. On the form, members of the team are required to enter their working locations for the upcoming week. I know through built-in metrics in Forms that the form takes under a minute to complete so am confident it isn’t really adding any extra work for the team.
Power Automate
Power Automate is a cloud-based tool from Microsoft. It provides a simple, effective interface to create and automate workflows and repetitive tasks. I used it to get my data where I needed it. The automation is called flows and can be created from templates using a GUI with minimal if any code required. In my case, I started with one of the predefined Forms to SharePoint templates. I only had to make a few slight changes to create a Flow to fit my needs.
Flows are built on connectors, which are essentially wrappers around an API that allow the service to communicate with Power Automate; there are a plethora of free and premium connectors to all sorts of applications. For my use case, I needed the Microsoft Forms and SharePoint connectors. When the user clicks submit on my form, Power Automate triggers my flow to capture and store the submitted data in a SharePoint list. Power Automate makes API calls using the aforementioned connectors, one to capture the newly submitted form results, and another to insert the data into my SharePoint list.
Power BI
Now the data is capture in SharePoint, I needed to create a dashboard that I could distribute to the wider team. Power BI, Microsoft’s data visualization offering seemed like an ideal fit and kept me in Microsoft’s Power Platform. Using Power BI, I was able to connect to the SharePoint list and apply a few transformations to create a flat table containing the staff location data. I also needed to code in some logic to ensure only the most recent submission for each week was being shown. This was one of the requirements; to give the team the ability to change their submission, should they need to. Finally, the Power BI report was embedded into our SharePoint intranet for the team to interact with, using the prebuilt Power BI web part.
Wrapping up
This small project has me quite excited about Power Automate. At the outset, I was already quite proficient with Power BI, but it was great to expand my knowledge of the Power Platform to include Power Automate. Looking through the list of connectors I have only scratched the surface of its potential. However, it was a great opportunity to experiment with some automation. I am excited to attend the Microsoft Power Friday 2020 in Auckland towards the end of August. There I will hopefully learn even more about what the Power Platform can do.
I’ve certainly got a few more ideas for automation!
