5 Practical Power Automate Examples Your Business Will Love

Formerly known as Microsoft Flow, Power Automate is a web-based service that helps to create automated workflows between your favorite apps and services to synchronize files, get notifications, collect data and more. Power Automate is a part of the Office 365 suite and is available in most Office 365 subscriptions. While its new name does not roll off the tongue quite like the old one did, it certainly flows (pun intended) with the Power Platform.

For those that have a deep SharePoint Server background, you might think that Power Automate is a replacement for the SharePoint Designer workflow. For us SharePoint aficionados, that’s certainly a selling point. But while Power Automate does provide you with a rich UI tool to handle automated processes in SharePoint, it is really a universal automation tool that connects all kinds of applications by utilizing custom and standard connectors.

So, what exactly can you build in SharePoint by using Power Automate? In this blog post, I will demonstrate 5 practical Power Automate examples that maximize SharePoint functionality.

For these use cases, we will create a couple of solutions for DevFactonaut, a fictitious company, and discuss various automation scenarios that provide value to its specific departments.

1. Deploying Lists and Libraries with Power Automate

If you wanted to reproduce a site structure in multiple environments, Power Automate is great for deploying lists and libraries. For example, you might need to provision several lists and several libraries to promote a Site Lifecyle Management process through environments used for Development, User Acceptance Testing, and Production. Another use case scenario could be that as a consultant, you might want to build out a structure on your development tenant and deploy the structure to a client’s tenant using Power Automate (assuming the option is available for you).

You can also choose to complement the Power App you built that has a data connection to a SharePoint site containing lists and libraries. Power Automate would deploy the dependencies associated to the Power App. Typically, you would execute a PowerShell script but this is another option to consider when automating these site artifacts rather than performing the task of creating the lists and libraries manually.

1. First, set the trigger for this flow to Manually trigger a flow.
   I chose this as our trigger because we want to only run this flow manually to provision the dependencies to a targeted site.
   

2. Next, hover your mouse on the arrow downward and click the (+) to add an action after the trigger.
   
   We will initialize a variable that would be used to define the targeted site.
   Optionally, you can opt out of this Initialize Variable action, and choose to create inputs on the trigger by clicking the + Add an input, see the Manually trigger a flow image above.
3. Next, we will define the list or library you want to create. For this, we will use the Send an HTTP request to SharePoint (SharePoint) action ALOT.
   The following properties within this action are populated using the REST API,POST https://{site_url}/_api/web/listsPOST https://{site_url}/_api/web/listsdocumented here.
   Based on the example shown above, it would help us determine how to populate our Send an HTTP request to SharePoint (SharePoint) action.

    

   • Site Address – populated using our variable called varSitePrefix.
   • Method – set to POST as we will be using to create lists, and libraries.
   • Uri – set to this value: /_api/web/lists to create the list or library.
   • Headers – contains information regarding the connection of the HTTP request such as the connection type, authentication token, proxies, etc.
   • Body – specifies the data related to the POST request.

   
   The Base Template of 100 is the Template ID and it would be using the Custom List template. For your reference, below is a table outlining the corresponding Template ID to a few List Templates.

   Name	Template ID	Description
   Announcements	104	A list of news items, statuses and other short bits of information.
   Calendar	106	A calendar of upcoming meetings, deadlines or other events. Calendar information can be synchronized with Microsoft Outlook or other compatible programs.
   Contacts	105	A list of people your team works with, like customers or partners. Contacts lists can synchronize with Microsoft Outlook or other compatible programs.
   Custom List	100	Using a list gives you the power to share information the way you want with your team members. Create your own list from scratch, add any other columns you need, and add items individually, or bulk edit data with Quick Edit.
   Document Library	101	Use a document library to store, organize, sync, and share documents with people. You can use co-authoring, versioning, and check out to work on documents together. With your documents in one place, everybody can get the latest versions whenever they need them. You can also sync your documents to your local computer for offline access.
   Form Library	115	A place to manage business forms like status reports or purchase orders. Form libraries require a compatible XML editor, such as Microsoft InfoPath
   Issue Tracking	1100	A list of issues or problems associated with a project or item. You can assign, prioritize and track issue status.
   Links	103	A list of web pages or other resources.
   Picture Library	109	A place to upload and share pictures.
   Survey	102	A list of questions which you would like to have people answer. Surveys allow you to quickly create questions and view graphical summaries of the responses.
   Tasks	171	A place for team or personal tasks.
   Wiki Page Library	119	An interconnected set of easily editable web pages, which can contain text, images and web parts.

4. Next, we will continue to use the Send an HTTP request to SharePoint (SharePoint) action to create columns for the list. This time the properties for this action will contain the following:
   • Site Address – populate using our variable varSitePrefix
   • Method – set it to POST as we will be using to create the columns
   • Uri – set to: _api/lists/getbytitle(‘<Site Title>’)/fields to create a custom field to a list or library
   • Headers – should contain information regarding the connection of the HTTP request such as the connection type, authentication token, proxies, etc.
   • Body – should specify the data related to the POST request

   Refer to the FieldType Reference link listed at the end of this blog post to identify the FieldType Number to the Field . Now, let’s create a Status field with a datatype of Choice that contains choices of In Progress, Approved, and Rejected.

   

5. To create additional columns for your list, continue to use the Send an HTTP request to SharePoint (SharePoint) action and repeat the steps we did for the Status field. When you’re done, you could group all the actions together using a Scope (Control) action for easier interpretation when reviewing your flow. The Scope action is helpful to group actions and conditions in your flow especially if your flow is complex and is too busy to understand from a glance.
6. Lastly, click the Save button when you are done defining the lists, libraries and columns to be deployed from this Power Automate flow. Now, you can run the flow manually to have it provision the lists, libraries and columns defined.

If successful, then the SharePoint site would appear to show the Vacation Request list and a Status custom field. I created additional columns that will be used in the next use case.

2. Populating a List OR Migrating a simple Excel Table contents to a SharePoint List

If your organization is planning a migration to Office 365 this can come in handy. You might already have a defined file plan for files that will be migrated to a specific Document Library or folder, but what about those simple excel tracking sheets? A Simple Excel file contains a large table without macros, conditional formatting, VB code, or referencing to another sheet. Using the SharePoint UI to perform a bulk import to a SharePoint list can be tedious and inconvenient especially if you do not already have the privileges to execute a PowerShell script. One option to resolve this is converting the Excel table into a SharePoint list.

In the previous example, we created a simple Vacation Request list. Now, we will add on to it a list to demonstrate the migration from an Excel table to a SharePoint list.

1. Set the flow type to instant and use the Manually trigger a flow After the trigger, add the List rows present in a table (Excel Online Business) action. This action queries the rows from the table in an Excel file.
2. Next, you’ll need to upload the Excel file to either OneDrive or SharePoint and define in the Location property of this action. In this example, I uploaded the file in the Documents library of the SharePoint site that contains the Vacation list. Then we will comb through each row using an Apply to each (Condition) and perform a Create item (SharePoint) action to the Vacation Request list. The condition will step into each row returned from our List rows present in a table (Excel Online Business)

When running an instance of the flow, you might encounter this error when dealing with Date columns in an Excel Table.

{

"status": 400,

"message": "String was not recognized as a valid DateTime"

}

{

"status": 400,

"message": "String was not recognized as a valid DateTime"

}

This is because the flow is reading the value of the ‘Start Date’ column and ‘End Date’ column as a serial number of the date. For example, the serial number 43946 represents April 25, 2020. To resolve this error, we will need to alter the column format from Date to Text or copy and paste it in a new column of Text format. Rerun the flow once the changes have been done to the file. Once your flow is completed successfully, navigate to your SharePoint list to view all the newly created list items.

3. Provisioning an AAD (Azure Active Directory) User with Power Automate

Another example of a common SharePoint and Power Automate automation is provisioning new users in Azure Active Directory. This is a great way to automate the onboarding process that is helpful to both HR and IT departments. For this use case, we will provision a new user once a department manager submits a New User request.

The request or service ticket to setup the Active Directory Account can be stored in a SharePoint List or any 3^rd Party application and will trigger the flow once the Status is set to Approved. To keep the scope of this use case simple, I’ll refer to a “Click a button to create a new Azure AD user account” template that is available in the Templates section.

Keep in mind that the account used to create this automation will need the necessary permission to use the Azure AD connector used in this template.

The trigger in this template has inputs that prompt the user running a new instance of this Power Automate flow. For simplicity’s sake, I’ll call this user the flow initiator. The flow initiator is prompted for the following input properties and their responses will be used as the properties in the Azure Active Directory User.

• Mail Nickname (i.e. username)
• Display Name
• User Principal Name (UPN)
• Given Name
• Surname
• Business Phone
• Mobile Phone
• Department
• Job Title
• Office Location
• Preferred Language

1. First, create a password for the user (it is a Initialize Variable (Variable) action), which autogenerates a password for this account with a concatenation of the text “Pwd” and a formula of substring(utcNow(), 20,8).
   
2. Next, use the Create User (Azure AD) action to populate the properties based on the responses entered in the inputs from our trigger. Once this action is successfully completed, send an email to the flow initiator with the autogenerated password for the newly created account.

So, what does this workflow look like in practice? In this scenario, the flow initiator is likely an HR professional or a Hiring Manager. This person receives the email sent in the last action, Send an email (Outlook) and pass it on to the new hire. After the new hire goes through an orientation session and gets settled at their workstation, they get a print out with their credentials from the flow initiator.

4. Automated approval process – From approval request to a response

The days of printing out a Vacation Request form, filling it out, and leaving it on your manager’s desk for are hopefully a thing of the past. With Power Automate, organizations can modernize legacy ways or mundane processes and introduce technology effectively.

In this example, we will use Power Automate to start the approval process for a type of document, notify the approver that there is an action task pending their approval, and allow the approver the option to respond on their workstation or on a mobile device using the Outlook app or the Power Automate app. Both apps can be downloaded from the iOS App store or Android Play store.

To demonstrate the approval request and approval response using Power Automate, let’s modify the Vacation Request list that we used previously.

1. For this flow, use the automated flow type and the When an item is created (SharePoint) This means that whenever a new item is created in this list, it will trigger an approval flow requesting an approve/reject action from the manager.
2. Next, add the Create an approval (Approvals) action and the Wait for an approval (Approvals) These two actions represent the requesting portion of this use case. I choose the Approve/Reject – First to respond choice for the Approval type. Alternatively, you could define custom outcomes instead of ‘Approve’ and ‘Reject’.
3. If you have a requirement where there are multiple Approvers and the Approval must run in parallel, then you could wrap all the approval actions in a For each {approver] condition. To do this, click the ellipsis menu of the for each condition and click Settings. Set the Override Default toggle to true under the Concurrency Control setting and set the degree of parallelism to the max. By default, a ‘for each’ condition is executed in sequential order. To learn how to accomplish this, read the Advanced | Flow of the Week: Send parallel approval requests to a dynamic set of approvers blog post.

For the sake of simplicity, we will keep the scope of our flow to only one manager. In our example, the manager receives an email notification regarding the request. Then, they respond directly in the email by clicking either the Approve or Reject button shown in the body of the email, optionally, they add a reason and then click the Submit button.

Depending on the response of the manager, the flow will move on to the outcome chosen. At which point, we change the value of the Status column to either Approve or Reject and send an email notification to the Requester (Created By – Email Address).

5. UI Flows in Power Automate – Intake form process to on-premise data source

UI Flows in Power Automate allow you to create and record Robotic Process Automation (RPA) capabilities to automate repetitive tasks in Windows and Web applications. With RPA, organizations can streamline business operations, reduce cost, minimize errors, improve compliance and productivity. Think for a moment of a task you need to complete on a reoccurring basis. Perhaps, you generate monthly financial reports for the financial department, or an inventory report for the procurement department, or you just want to get more done in less time. RPA is the way to do it. In this example, we’ll demonstrate the automated process of routing content from a Microsoft Form to an on-premise data source, Access.

For our next scenario, our fictitious DevFactonaut company wants to automate its sales process.  DevFactonaut has a Contact Us form on their public facing website. The form, built using Microsoft Forms, is configured for anonymous use. Potential customers visit the company website, fill out the form, and connect with sales about the services offered.

To handle that, we will set up a form that upon submission triggers a flow that saves the entry in an Access database file stored on the company’s server. We will be using Power Automate, UI Flows (Desktop), Microsoft Forms, and Access.

1. First, let’s build the form using Microsoft Forms by signing into https://forms.office.com/ or clicking the Microsoft Forms from the Office 365 App Launcher.
2. Click the New Form button and build the form with a title of Contact Us, provide a description of the form, and add fields onto the form as shown.
3. In the top right corner click the ellipses and the settings to allow anonymous responses and a customized thank you message. Optionally, you can pick a theme or set a custom theme.
4. Next, we will create our flow that will be triggered to route the contact request from Microsoft Forms to our UI flow that will store the value in the Access database. Navigate to Power Automate by going to https://flow.microsoft.com/ or clicking the Power Automate from the Office 365 App Launcher. Click the My Flows section shown in the left navigation pane, navigate to the UI flows group, and click the Create a UI flow
5. You’ll have the option of choosing a desktop app or web app. Choose Desktop app and click Next. Define a name for the flow, for example; Store Contact info to Database. Click Next.

6. Afterwards, you’ll be prompted for the inputs fields that would be entered to the Access Database. Simply taking the values from our Contact Us form as our Input fields to be used in our recorded UI flow.
7. We will then record the steps that the UI flow will perform. Make sure that you have downloaded and installed the package needed to record the steps.

8. You might have to restart your workstation after the package is installed.
9. A recorder control will appear and docked at the top center of your screen. Click the Record button when you are ready to perform the tasks.
10. Click Done when you’ve finished your recording of tasks.
11. Click Next if you are satisfied with the recording. In our scenario, we won’t define any outputs as we are just submitting values from our Microsoft Forms (accessible outside our organization) and saving the contact info in our Contacts Management Database file. If you’d like, perform a test to validate that the recording will execute without any errors.
12. Since our test is successful with our UI flow, the final step is to create an automated flow and run the UI flow when a new entry is submitted from the Microsoft Form. We will need to configure a On-premises gateway for the UI flow to connect to the server. For instructions, read how to install an on-premises data gateway.

This automated flow type uses the When a new response is submitted (Microsoft Forms) trigger. We will then add the Get response details (Microsoft Forms) action to retrieve the fields from the Contact Us form. The last action will be the Run a UI flow for desktop (UI flow) action, and we’ll map the Microsoft form fields to the UI flow’s input fields. This action is a premium action, and you’ll need to validate if you have the sufficient licensing associated to your account.

With minimal effort, we were able to get a working UI flow to be recorded, have it invoked from an automated flow, and the automated flow to handle the data intake submitted from the Microsoft Forms. You may have reasons for and against using Access database. Here, I used Access, to show you that you can collect data externally and store it in an internal database.

Final Thoughts

So, what did you think? Does this interest you? Do you see possible scenarios on how to integrate these demos in your organizations? Let’s connect and continue the conversation.

That’s a wrap! We went through 5 use cases done with Power Automate in a very grounded and simplistic scope. However, I hope this gives you insights and showcases the powerful automation of Power Automate. Pun intended.

5. • Working with lists and list items with REST
   • Complete basic operations using SharePoint REST endpoints
   • List Template Types
   • Fields REST API reference
   • FieldType enumeration
   • Create parallel approval workflow with Power Automate
   • Advanced | Flow of the Week: Send parallel approval requests to a dynamic set of approvers
   • Install an on-premises data gateway