Introduction
The year 2022 can be said to be the rise of Artificial Intelligence such as ChatGPT, and with the ever-growing capacity, the world is heading to an era of quick and fast decision-making. Organizations all over the world are beginning to adopt different forms of AI and Automation in their daily activities.
The introduction of Power Automate Desktop in 2019 by Microsoft was designed to help employees automate repetitive and time-consuming tasks by creating various workflows to perform them.
Prerequisite
To follow along with this article, you must have basic knowledge of Power BI Desktop and Power Automate. You should also have Power Automate Desktop already installed on your PC, this will be a vital part of the entire project.
It should be noted that Power Automate Desktop works for only Windows 10 and 11(Including future releases).
Solution Architecture
Let us use a case study, you are a data analyst for a Pizza🍕 distribution company all over Europe, and you want to see the latest report on your Power BI desktop at exactly 9:00 AM (UTC +1) but you resume work at 8:45 AM (UTC + 1). This entire process needs to be automated with little or no human effort.
Our data source will be cloud-based on Microsoft SharePoint, the visualization tool used is Microsoft Power BI, the automation tool used is Power Automate Desktop and lastly a cloud-based schedule trigger is created using Power Automate (Flow) to activate the entire process.
Understanding Microsoft Power Automate
Microsoft Power Automate (Cloud) and Power Automate Desktop (PAD) are both on-code/low-code applications that can help you work smarter by automating your repetitive, manual processes like clicking and various events on Outlook and Excel.
Power Automate
The Microsoft Power Automate, previously known as Microsoft Flow until 2019 is a cloud-based platform (SaaS), for automating recurring tasks by creating automated workflows between different apps and services. It integrates with a number of cloud-based services, including Microsoft Office 365, Dynamics 365, SharePoint, and OneDrive, and lets you develop and manage workflows using a web-based interface.
Below shows an image of the various connectors supported by Microsoft Power Automate.
Power Automate Desktop
Microsoft Power Automate is a desktop application that enables you to automate processes on the local machine or distant computers (VM). It has a graphical interface that allows for easy creation of flow and communicates with a variety of programs and services, such as file systems, databases, and web browsers.
It supports both On-premise Gateway and Machine-runtime apps that allow the connection between the Power Automate Desktop and Power Automate cloud to fully utilize RPA’s (robotic process automation) capabilities.
Power Automate Desktop Interface
The interface can be split into major categories.
- Actions: This is located at the left canvas of the desktop app, and usually indicates what actions you want to perform.
- Variables, UI Element, and Image: This can be found at the right corner of the canvas. Each action is represented with a variable, UI Element is used in engaging with software and websites without using absolute coordinates or image recognition, and lastly, an image which indicates the image stored using the RPA software.
- Main Canvas: This is the flow, where all the actions are arranged as you want them to happen.
Task Perform by Power Automate Desktop
Power Automate Desktop can be used in performing a variety of tasks:
- Extracting Data from a PDF, MS Word, and another format into a spreadsheet.
- Extracting Data from the web using the RPA process.
- Launching applications at different intervals.
- Executing PowerShell code
Let Get Started
From the project architecture, we are trying to automate the execution of the Power BI Desktop application daily.
Installation of Power Automate Desktop
The installation of Power Automate Desktop is like all applications. First, click on this link which will direct you to the official Power Automate site, formerly known as “flow”.
In your Power Automate site click on the “My Flow” icon, which can be found on the left side canvas. In the “My Flow environment, click on Install and select “Power Automate Desktop.”
After downloading the application, follow the installation process.
Create Flow in Desktop App
From the project case study, we have a report we want to automate and get the latest data at exactly 9:00 AM UTC + 1 daily.
Step 1: Create New Flow
In your Power Automate Desktop, click on “New Flow” at the top left corner. A new window will appear.
In the new window, you are expected to provide it with the following information and click on “Create.”
Step 2: Run the Application
Now, on the main canvas, we need to create a flow that will open the Power BI report in your local machine.
The “Run Application” flow is gotten from the Actions section of the application. In the Actions section search for “Run Application,” then drag it to the main canvas.
Set Parameter
To run the application, we need to set parameters like directory and time delay.
- Application Path: This is the “.exe” path of the Power BI desktop we want to use.
Note: To get a seamless experience I will recommend you use the Power BI got from the official Microsoft site and not the one from the Microsoft store.
Right-click on the Power BI desktop app icon and select “Open file location,” this should take you to the Power BI bin directory.
In the directory scroll till you find the “.exe” file, then copy the directory and paste it into the Application Path.
- Command Line Argument: This is the “.pbix” report we want to automate, you are expected to also navigate the file directory and copy the file path.
- After Application Launch: This was set to “wait for the application to complete” before coming to the next action.
- Timeout: This is optional, but due to the size of the Power BI application the timeout was set to 60 seconds, this is aimed at making the application settle before going to the next action.
Step 3: Recorder
Each action is recorded separately while the recorder monitors mouse and keyboard movements with respect to UI elements. The recorder can produce both UI and browser automation actions throughout each recording session.
To start the recorder, click on the recorder button at the top of the main canvas.
Note: At this stage, your Power BI report should be open. The GIF below shows the different actions performed. You will notice the Record action pane on the right side, this is used to record every action (Refresh and Saves) conducted using the recorder.
As data are populated daily in the SharePoint folder, we want to see the latest update, which is why the Refresh icon is selected and we click on save to keep the update.
Check out our past article on how to connect Power BI to the SharePoint folder.
Step 4: Delay
A wait time of 2 minutes (120 seconds) is added in between the Refresh and Save actions, you also can reduce the time depending on your use case. This is due to the fact we cannot be sure how long the refresh time will take so we need to take that into consideration.
Step 5: Success Note
This is the last action; we need an indicator in the form of text that the state flow ran successfully.
Test Flow
To evaluate if the flow we just created works perfectly well click on the “Run” icon at the top of your main canvas, this should activate the entire process.
Let us assume the marketing team just updated the SharePoint folder with yesterday’s sales and you want to see the updated sales in your Power BI Report. The GIF below shows the entire process.
Schedule Flow
Now that the flow works perfectly, we need to create a trigger that activates the RPA process in a recurrent manner. This can be achieved using a “Scheduled Cloud Flow” of Power Automate.
Note: To use this feature, you need to be using a “Premium License.” You can try out a trial version for 60 days and upgrade later.
The flow steps will provide a step-by-step process for achieving this.
Step 1: Create a Schedule Flow
You can define a recurring pattern, such as daily, weekly, or monthly, as well as the start time and time zone when scheduling a flow in Power Automate.
In your Power Automate site, click on “My Flows,” then select the drop-down and pick Scheduled cloud flow.
In the new window, you are expected to fill in the following information.
Step 2: Run Flow in Power Automate Desktop
We need to create a flow that will help activate the Power Automate Desktop (RPA) we just created. Click on the new step, which will provide you with a variety of actions.
Select the Run Desktop flow then click on “Run flow built with Power Automate Desktop.”
Step 3: Set Connection
This is a crucial stage in the connection process we need the necessary information.
- Connect: For the connection mode, this is what will serve as a link between the cloud service and Power Automate Desktop. We have two options for connection, which are the Gateway or Power Automate Machine Runtime.
- Gateway: The on-premises data gateway serves as a conduit for efficient and safe data transmission between various Microsoft cloud services and on-premises data (data that is not in the cloud).
- Power Automate Machine Runtime: This enables you to connect directly to your Desktop computer without the need for a gateway.
To use the machine feature, ensure you are using the latest version of the Power Automate desktop. In your PAD click on the settings at the top right corner and scroll until you find Machine Settings. Remember this feature is only available for premium users.
To create a new Machine in your Power Automate cloud, expand the Monitor tab, and select “Machines” then select create new and follow the procedure.
- Machine or Machine Group: This is the name of the Machine group we just created.
- Domain and username: These are the username of your local machine. To get the username click on your Windows tab and open the command prompt.
Type the keyword “whoami”. This should generate your username.
- Password: Provide your Power Automate login password.
Step 4: Identify Desktop Flow
Select the flow you want to use in your PAD and Run Mode.
Test Cloud Flow
Click on the Test icon at the top right corner and select Test manually. This should start the entire flow process.
Monitor
From the image below you will notice the flow ran successfully.
Conclusion
In this article, we have learned how to automate Power BI report to get the latest information by creating a flow in Power Automate Desktop. The data source used was connected to a SharePoint folder. Lastly, we automated the entire process using a cloud flow schedule trigger.
To learn more about Power BI, you should sign up for our classes, keep up with the blog, and subscribe to our YouTube channel.