"Can I write back to my data source, change specific fields, update data directly from Power BI?"
I hear this question a lot from Power BI users. Power BI is an amazing BI tool to visualise data, but you can go the extra mile if you take advantage of all the capabilities the Power Platform can give you.
Today, we are going to solve the problem just mentioned, we are going to give users the ability to write back to data sources directly from Power BI using PowerApps.
As an example, I will be using a very simple database I created in Azure SQL. This database has data about used cars. Our imaginary Power BI customer wants to be able to change the price of a car directly from Power BI, and have it updated instantly in the report.
This is the current report they have:
To achieve the "instant data refresh", our connection to SQL Server is done using Direct Query.
Now that we explained our scenario example, let's get to action!
1) Adding the PowerApps visual to your report
When it comes to embedding PowerApps in your Power BI reports, there are two options: you can embed an existing app in you report, or create a new one. In this example, I will create a new app for my report.
To do this, I added the PowerApp visual to my report page, added the fields I want to use in my app, and selected "Create New".
To add a PowerApp to your report, follow the steps on the video below:
The fields you choose to add to your PowerApp now will be available for you to use when you're developing your app, in other words, the fields you add to this visual will be the only fields the PowerApp will "understand". I decided to add all the fields I have, just in case!
When you click on "Create New", a message with an "Open Browser" warning will show up. Click OK to open this new browser window:
An App based on your inputs will be automatically created for you. The important part to understand here is that this PowerApp will have a special connection called "PowerBIIntegration" as shown at the end of the video (left hand side, Tree view).
This connection is what will allow your Power BI report to "talk" with your PowerApp. Make sure you can see this object!
Also, make sure that the Items property of your gallery is defined to:
'PowerBIIntegration'.Data
2) Create a List Screen in PowerApps
In this step, we will be creating a screen that will allow the users to browse different cars.
To do this, we are going to improve the screen that was automatically created for us.
As we have our integration with Power BI set up already, to use any of the fields from your Power BI report you just need to use the function ThisItem.field:
A few things I did to make my App look nicer:
> Change the Theme
> Add a Label as the header of my App
> Add an Image to the Gallery so that I could see the photo of the car
> Added more labels to the gallery to show more information about each on of the cars using the Text property and the ThisItem. function.
One of our screens is finished! Nice work! Now, to test our App in Power BI we need to save it first. You will only be able to see your new app in Power BI once it's saved!
To save your App go to:
File > Save As > Choose the option "The cloud" > Name your App > Click on Save
You can also choose to Publish and Share your app after you save it.
Back to Power BI, this is how my report looks like now:
If you're asking yourself "So the app just shows up there, automatically???": Yes! It does! As we created your app from within Power BI, once you save it, everything just shows up like magic!
You can also see the power of the PowerBIIntegration object here. When you select a category in a visual, the app will filter the results automatically!
3) Create an Edit Screen in PowerApps to edit the data directly from Power BI
Getting to the fun part of our tutorial! Now it's time to create a Form Screen, so that the users can edit the price of a car from within Power BI.
So let's add our Form Screen to our app:
We will be using this screen as the Edit Screen, in other words, this is were we will be inserting the values that will be written back to our database.
For us to be able to do this, we need to connect this form to our original Azure SQL Database:
After choosing SQL Server, click on "Add a connection":
Add the details of your connection and click "Connect":
Once your connection is successful, another window will show up where you can select the table you want to use in your connection.
You probably noticed that your new screen didn't change even after you connected to your database... Well, that's because you only defined the connection, but we didn't yet define the data source for this specific screen.
So, let's finish up our connection and add a few fields to our edit screen:
4) Format our new Edit Screen
Once again, our Edit Screen doesn't look that nice yet. We will just be formatting it a little bit so it's more user friendly. We will be changing some of the fields properties from Edit to View so that users can only change the price and not any other property:
Looking good now!!
But... wait... I just tested our Edit Screen and it's not showing anything... What is happening?? Well, the reason is that our Edit Screen still doesn't know which items to show, and we are still not "talking" to the Gallery Screen we previously created.
To make our Edit Screen work with our Gallery Screen, we need to change the Item Property of our Edit Screen to:
LookUp('[dbo].[Auto_Trader]', CarNumber = BrowseGallery1.Selected.Car)What is this formula doing exactly?
We are looking up the value from our data source (in this case CarNumber), for the selected car in the Browse Gallery.
5) Tie everything together
So, we're almost done! There are a few things missing though.
We still don't have the navigation between the screens defined, and we also need to make sure that when a price is edited, it is automatically reflected in our report.
So, starting with defining the navigation between screens:
To make Power BI refresh the report automatically, we just need to change the On Success property of the Edit Form to PowerBIIntegration.Refresh(), yes, it's that simple! This function will refresh the visuals automatically when the form is successfully submitted, but be careful, this works because we are using a Direct Query connection! If you're using Import Mode then you might want to look at Power Automate to do this for you.
We are also going to tell our PowerApp to go back to the main screen once the task is successful:
Don't forget to save your App, and also Publish it and Share it with the users who will be consuming it!
5) Testing our report/app
So, it's time to see if our app is doing the magic it should! To see everything in action, first publish your report to Power BI Service.
My final report:
(If you have troubles with your app, try closing your PBI report and opening it again. The changes you do in your app might take some time to reflect in Power BI. Last case scenario, you can delete your PowerApp visual, add it again and this time select "Choose App" instead of creating a new one).
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!