Write Back in Power BI using Power Automate Visual

Introduction

Microsoft Power BI is adding new trending innovations for its users every month. In April 2021, Microsoft announced the Power Automate visual for Power BI reports which helps Power BI to integrate with Power Automate and its Flow, allowing end-users to interact with another bunch of applications like Microsoft Teams, SharePoint Online, Adobe PDF Service, Twitter, etc.

In this article, I am going to show you how to use Power Automate visual to write back data (update data from the Power BI tool to the original source) from the Power BI dashboard to the SharePoint Excel file source. I am assuming that you already have Power BI basic or intermediate level knowledge, and you already know how to connect to SharePoint Excel, and how to publish reports to Power BI service. I’ll go through all the steps to enable write-back capabilities for our Sharepoint Excel data source.

Before I even start explaining how it works, let me give you some background about the requirement I had received from one of my client.

Problem Statement

I had developed a really good dashboard for my client to provide all the meaningful data insight for their Ticket Management System. They were very happy overall with reporting. However, they were only able to use the dashboard to plan and make decisions on their action items with respect to underlying data but they were not able to perform it immediately on the Power BI dashboard itself. If they wanted to change the status of any ticket, they needed to open another application which led to switching between applications multiple times.

Now it’s time to see how to achieve Write Back in Power BI using Power Automate.

Let me walk you through the process explaining each step.

Process Summary

1. Import Excel and Power Automate Visual
2. Configure instant Cloud flow
3. Add Refresh a dataset step
4. Publish & Test it

Below is a sample table available on the SharePoint site which has new ticket data.

We will import the same Excel data from Sharepoint in Power BI and will see how we can implement the functionality to write back so that end users can change the status of the ticket from Power BI itself using Power automate visual. (I will not focus on importing data into Power BI from the SharePoint site as our main focus point is Power Automate visual).

Step 1. Import Excel and Power Automate Visual

1. We need to import the SharePoint Excel data into Power BI.

2. Click on the three dots under the Visualization section and select Get more visuals option as Power Automate is available as a custom visual in the AppSource.

3. In AppSource, search for Power Automate. Power Automate (Preview) will be available in the list of custom visuals. Click on Add button to add it to the visualization pane.

4. Once the Power Automate visual is imported (a popup will tell you that this happened) it will appear in the visualization section.

5. Click on the Power Automate visual to add it to the visualization pane.

Step 2. Configure instant Cloud flow

1. To start the flow, click on the More options, and click on Edit.

2. Drag and drop the Ticket ID and Ticket Status field in the Fields section.

3. Click on the New button option and select Instant cloud flow.

4. We can start building our flow in which we can see the first step is already added.

5. Click on the New step button and search for Update a row action.

6. Fill all the required fields in the Update a row action one by one.

7. I have copied my Excel file under SharePoint Site -> Documents. Copy the SharePoint site URL till site name and Past in the URL in Location section under Update a row activity.

8. In the Document Library section, select Documents.

9. In the File section, select file New Ticket.

10. In the Table section, select table New_Ticket.

11. In the Key Column section, select table field Ticket ID as based on Ticket ID we will change the status.

12. In the Key Value section, insert the parameter Power BI Data Ticket ID which is available as a parameter because we had drag and drop Ticket ID in Power Automate Visual from the Power BI dataset. As soon as you add this step, Power Automate will automatically add the looping step as Apply to each so that if we try to change the status for multiple tickets we can do so in one go.

13. As soon as you add Power BI Data Ticket ID in the Key Value section, Power Automate will automatically add the looping step Apply to each so that if we try to change the status for multiple tickets we can do so in one go.

14. In the Ticket Status section, put the value as Active. That means whichever Ticket ID user will select we will change the Ticket Status to Active directly in the source Excel file.

15. Before we add our final step in flow we need to rename our flow as WriteBack and save it. And then publish our PBIX file to the Power BI service in My workspace.

Step 3. Add Refresh a dataset Step

We will add a new step as Refresh a dataset, so that we can refresh our Power BI dataset once users update the ticket status.

1. Same as previous steps, we will set the New Step option and add the Refresh a dataset step at the end of the flow. Click on Save and Apply.

2. Go back to the visualization pane and format the Power Automate visual and change the button name to Make Active.

Step 4. Publish & Test it

Finally, after all the efforts we put in here, now we will be able to see how it is actually working.

1. We are almost done here, now save & publish the report again to My workspace.

2. Go to Power BI Service and open the report.. https://app.powerbi.com/

3. Click on Ticket ID = 3 to make it an active ticket in the backend Excel and refresh the report to see if it actually changes the ticket status in backend Excel too.

4. Click on the Make Active button to trigger the flow.

5. Let’s first check in the backend source Excel file if the status has changed for ticket id =3.

Great!

6. It has changed in backend Excel, now let's see if it has been also changed in the Power BI service

Awesome!

It’s working in the PowerBI service as well.

We can also select Multiple Ticket IDs and change their status in one go.

Conclusion

With this new Power Automate Visual integration, now Power BI users have Write-Back capabilities through the Power BI interface itself, due to which now, Power BI is not just an analytical tool to show the data insights but also has the capability to change the underlying data based on the user action.

In this blog, we first saw a report that has ticketing information, however, end-users were not able to update the backend Excel data. To update that data, we used Power Automate visual so that end-users can change the status of the tickets from Power BI itself.