Automating ETL, Power BI Reporting and Sharing with Power Automate

About this project

PREAMBLE

“We have an Excel sheet for our costs on the product categories x, y, z, etc., and every week when we make changes, we need to recreate our tables to send to our stakeholders. Can we automate this process?”

“We have periodic Excel extracts on our sales, and every time we make a new export from the system, someone on the team needs to clean it, add it to our Excel database (Excel is not a database), re-run the pivot tables, create charts, and send them to stakeholders. Can you help with that?”

Who has never heard requests like these in the business world?

A while ago I was having conversations with a friend, that also works in data analytics, about how common these type of requests are, and how much many business teams heavily rely on Excel data. In fact, granting access to SQL servers to everyone in an organization is not feasible or secure. Some individuals may not know how to query data from SQL servers, and others may not even require access.

In such scenarios, people often come up with manual routines that might work, but the more manual work involved, the higher the risk of errors and inefficiencies becomes. Let's take a closer look at the two cases mentioned above.

Case 1: Managing Costs File

In this case, a team has a limited amount of data to control in an Excel file. They make changes to this file, and its output is widely used in the organization to extract useful information. For example, a team managing a costs file needs to send screenshots of different cost tables, one per product category, to everyone in the organization.

The risk of manual mistakes here is relatively low, limited to sending the correct screenshots or setting up the tables correctly based on dynamic data. However, this process is time-consuming.

Wouldn't it be better to simply change the costs where needed and have the stakeholders automatically receive the updated information?

Case 2: Exporting Sales Data

In this case, a team exports sales data from a system (e.g., Salesforce) on a weekly or biweekly basis. They need to clean the data, append it to another Excel file where historical data is stored, refresh a pivot table, create charts, and send screenshots to stakeholders.

The risk of manual mistakes here is much higher. There is a chance of making significant errors during the manual data transformation process, overriding historical data during copy-paste operations, and needing to train someone new if the person responsible leaves the company. Furthermore, this manual process consumes a considerable amount of time (some ETL flows, done this way might take hours) and has various potential drawbacks.

To address these challenges, a solution combining Excel, SharePoint, Power BI, and Power Automate can be implemented. In the following section, we will delve into the solution for Case 2, which is more complex, but we will also touch upon what could have been done for Case 1 when the two cases present differences.

SOLUTION

Step 1: Setting up the SharePoint Environment

To implement this solution, the team needs to have a SharePoint team site with a document library. Within this library, a folder should be created to store the Excel file(s).

For Case 1, since there is only one Excel file, placing it in the folder is sufficient.

For Case 2, the historical data needs to be divided into at least two different files (Extract_1 and Extract_2) and placed in the folder.

Step 2: Automating ETL with Power BI Dataflow

At this stage, I suggest creating a Dataflow to import the data into Power BI Service and automate the required transformations.

For Case 1, import the single file from the SharePoint folder into the dataflow.

For Case 2, the historical data files need to be combined using a Power Query rule. To achieve this, click on "Get Data > SharePoint Folder" and insert the link to the site (e.g., https://mycompany.sharepoint.com/sites/MY_TEAM_SITE).

In the interface that appears, locate the desired folder containing the files you need to import (Image 1.A).

Next, click on the double arrow in the [Content] column to set up the combining rule. (Image 1.B).

Image 1

The result will be a table (Image 2) with all the exports combined, effectively recreating the historical data.

Apply any other necessary transformations (e.g., adding columns with specific rules, filtering elements, finding and replacing characters, etc..)

Image 2

Step 3: Power BI for Automated Reporting

Once the Dataflow step is complete, proceed to Power BI desktop. Use the "Get Data > Dataflow > Workspace (Trial Workspace) > Dataflow Name within the workspace (Sales Data Dataflow) > table within the dataflow (Sales Data)" sequence to access the historical data.

Create the required visualizations based on this data and publish the report on Power BI Service (Image 3).

The example report in the picture 3 contains the available sales data, until January 30th, 2023.

Image 3

Step 4: Automating Data Refresh and Sharing with Power Automate

Suppose there is a new export of data after January 30th. Instead of redoing the entire ETL process and visualizations, we can simplify things.

A team member only needs to download the Export_4 file from the system (e.g., Salesforce), place it in the SharePoint folder, and the rest will run automatically in the background.

To set up the whole automation, login to Power Automate with Windows credentials, select "Create a Cloud Flow" by clicking on the "+" icon on the left side of the homepage.

Next, follow these steps (Image 4):

Image 4

SharePoint trigger: "When an item or a file is modified." This triggers the entire automation when a team member places a new file in the SharePoint folder.

"Refresh a dataflow": Instruct Power BI Service to refresh the dataflow, including the new Extract_4 file, appending it to the historical data and applying the predefined transformations.

"Refresh a dataset": Refresh the underlying data for the visualizations to be shared. Pass the updated data from the dataflow to the Power BI report used for visualization creation and sharing.

Next, use "Export to Files for Power BI Reports" (Image 5) to export the visuals for sharing. Choose the desired format (PNG in this case) and select the pages of the report to export as PNG image.

Note that the string to insert in the "Pages pageName - 1" box is the last part of the URL for the desired page in the Power BI report.

Image 5

The final step (Image 6) involves sharing the export with the intended audience. Use the Power Automate step "Send an email (V2)" to set the audience, subject, body, and include the attachment from the previous Flow's step.

Image 6

CONCLUSIONS

Returning to our initial situation, where periodic Excel extracts on sales required manual cleaning, database updating, pivot table re-running, chart creation, and stakeholder communication, we can say that significant improvements have been made.

Now, all the steps occur in the background, and team members only need to place their periodic export in the SharePoint folder.

An email with the updated charts is automatically sent to stakeholders (Image 7).