Using Power Apps in Power BI reports: Enabling writeback

The Power Apps visual is a powerful tool for adding writeback and other functionality from Power Apps into Power BI reports. This visual makes it possible for users to take action directly from Power BI reports and update the data in real time from the reports which then can be immediately reflected back in the reports. This ability to edit data directly from a Power BI report, brings another layer of functionality to Power BI reports that was not previously possible.

The Power Apps visual first became available as a custom visual in 2018 and then as one of the default visuals as of the October 2019 release of Power BI Desktop.

The Power Apps visual provides an important functionality to refresh a Power BI report page automatically which eliminates the need for the end user to manually refresh the page by clicking on the Refresh option from the Power BI menu to see changes in the data.

In this post, I will show you how to add a simple app to a Power BI report to update the data in the report and have the app automatically refresh the page. All of this can be done with a few lines of code thanks to all the work that has been done in Power Apps to make the app creation experience extremely user friendly and relatively easy to learn.

Design

Before we get started, let’s take a look at the overall design. Our goal is to create a Power BI report and add a Power Apps app (referred to as “app” for the rest of this post) so that we can update the report’s data and have the page refreshed automatically to reflect the changes we make.

In order for the page refresh to reflect the changes immediately, the table being updated by the app, must have its storage mode set to DirectQuery. If its storage mode is set to Import, the changes won’t be reflected with a page refresh in Power BI service until the dataset (data imported in the model) is updated by a dataset refresh. To read more about table storage modes, see this.

Process

Step 1: The user makes some selections in the Power BI report which provides context (current filter and row selections) to the app. This way the app can have some fields prefilled so that when the user wants to update records, the user does not have to type everything form scratch.

Step 2: The user submits the updates by clicking on a button(or icon) in the app. This will update the underlying SQL table.

Step 3: The app triggers a page refresh through a function called PowerBIIntegration.Refresh(). This function is the key to this design. In fact, the Power Apps have been available for use in Power BI for a while, but the integration part is a new feature (within the last year) and takes their usefulness to a whole new level.

Step 4: Once the page is refreshed, the user will see newly updated data reflected in the report.

Data Source

The examples shown here are based off of the AdventureWorksDW sample database. I added a sample sales table based on FactInternetSales. To keep things simple, this table has all the fields that the report needs. It does not contain any foreign keys to other tables. This way the focus of this post can remain on the report/app integration. (To learn more about setting up an app that uses more than one SQL table, see this post by Brian Knight.)

CREATE TABLE [dbo].[FactSmallSales](
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[ProductName] [nvarchar](250) NULL,
	[ProductSubcategoryName] [nvarchar](250) NULL,
	[CustomerName] [nvarchar](250) NULL,
	[SalesAmount] [money] NULL,
	[OrderDate] [date] NULL,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[FactSmallSales] ADD  CONSTRAINT [PK_FactSmallSales] PRIMARY KEY CLUSTERED 
(
	[SalesOrderNumber] ASC,
	[SalesOrderLineNumber] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

This table has a primary key on SalesOrderNumber and SalesOrderLineNumber. I will explain later why this is needed for the simple app to work. You can work with a table without a primary key in Power Apps but you have to do more work to update it vs. if you work with a table that has a primary key.

Insert into [dbo].[FactSmallSales]
Select
top 1000 
SalesOrderNumber,
SalesOrderLineNumber,
EnglishProductName  as ProductName,
dpsc.EnglishProductSubcategoryName as ProductSubCategoryName,
dc.LastName  + ', ' + dc.FirstName as CustomerName,
SalesAmount,
OrderDate
from [dbo].[FactInternetSales] fs 
Join DimProduct dp on fs.ProductKey = dp.ProductKey
join DimProductSubcategory dpsc on dpsc.ProductSubcategoryKey = dp.ProductSubcategoryKey
join DimCustomer dc on dc.CustomerKey = fs.CustomerKey

Power BI report

Once you have the FactSmallSales table ready, open Power BI Desktop and use Get Data to point to the FactSmallSales table. When prompted for storage mode, make sure you choose DirectQuery; otherwise the refresh won’t work as expected. Note: The model can have other tables in Import or Dual storage modes but the table to be updated must be in DirectQuery.

The following picture shows a sample report I created. I have one chart showing Sales by ProductSubCategory and a card showing total sales. The table visual at the bottom shows details of FactSmallSales.

At this point, it is very tempting to click on the Power Apps visual from the visualizations and add it; however, it is very important that you follow the recommended order of operations and deploy the report to Power BI service first.

After you add the visual in the service and connect it to a Power Apps app, you can download a copy to your machine and save it. Even then, the Power Apps visual cannot refresh a page in Power BI Desktop. The page refresh only works in the service.

Make sure to deploy the report to Power BI service first.

Adding a Power Apps app

Once you open the report in Edit mode in Power BI service, click on the Power App visual.

The visual provides very clear instructions on how to configure it.

First select the fields you want to make available to the app. In this case, select all the fields from FactSmallSales since we want the user to be able to edit all the fields in this table.

This next step is critical. You are given a choice of using an existing app or creating a new one.

A new app will always work properly for both data integration between the Power BI report and the app as well as the automatic page refresh by the app.

The existing apps fall into two categories and only one will work properly for the page refresh. If you watch videos posted online, you will see that some people can get an existing app to work while others say that you must create a new app.

• An existing app only works if it were previously created from the Power BI service.
• An existing app that was not previously created from Power BI will not integrate properly with Power BI. If you try to use such an app, Power BI will actually open the app and make some changes and it appears that it is going to work but even if you get the data integration part to work by manually adding some code, the page refresh will not work. The PowerBIIntegration.Refresh() is not available for apps previously created in Power Apps.

The moral of the story is: Always create a new app or use an app that you have previously created from Power BI service.

Here we will create a new app. To continue, click on Create New. This will open Power Apps Studio with a newly created app. The app has one screen with a Gallery on it that shows one item per row of FactSmallSales. You can preview the app by clicking on the play button on the top right corner of the studio.

As you can see, you already have a fully functional app showing one field (Customer Name) from the table. At this point, if you save the app and go back to Power BI, you will have an app that will interact with the report. If you click on the bar chart in the report, the app will show the customers for that ProductSubCatergory. This works because Power BI is passing context to the app.

In Power Apps Studio, take a look at what is created for you. On top of Screen1, you see PowerBIIntegration. This is the connector that makes it possible for the context to flow from Power BI to the app.

If you take a closer look at the Gallery, you will notice that its Items property is set to ‘PowerBIIntegration’.Data.

Let’s make some changes to the app to make it more useful and be able to update some records.

First let’s work on the Gallery. A gallery is very easy to work with. You make changes to the first item in the Gallery and the rest of it will mimic the changes. In this case, you can drag the bottom of the first item to make it larger and then copy and paste the Customer Name field several more times. Since each item in the gallery gets its data from the source of the Gallery (in this case PowerBIIntegration.Data), you can change the source for each field from CustomerName to a new field by modifying its Text property. There is inttelisence built in that helps you choose the fields.

The following picture shows how I designed the first tile of the Gallery.

Next we need to add a second screen to edit selected items from the Galley. Go to the Home tab and click on New Screen and choose Form.

This creates a new screen with one Form for editing. To learn more about Forms in Power Apps see this. 

A form has two important properties:

• Data Source: This is the table that will get updated.
• Items: This is where the form gets its fields populated from.

Note: Even though the fields are already coming from the Power BI report, you must connect the form to your SQL data source for it to be able to write changes back to the table

To set the Data Source, click on the Connect url in the form. This will guide you to create a new connection. Choose SQL database and connect to FactSmallSales.

(Reminder: If you are working with an On-premises database, you need to have an entry for the database in the On-premises Data Gateway)

Once you make the new connection, choose the connection in the Data Source drop down. Next click on Edit Fields and choose all the fields. The order you click on the fields is the order in which they will be added to the form. You can change that later from the Fields window.

At this point, you get a nice screen with a form with all the fields on it but the form is empty. To get the form to prepopulate from the selected item from the Gallery from the other screen, you need to set the form’s Items property. With the form selected, find the Items property and set it to Gallery1.Selected. This will populate the form.

You can change the title of the form by clicking on it. Change it from “Title” to “Edit Sales Order”.

So far we have a form that is showing the selected item from the Gallery. Let’s get the form to save its values to FactSmallSales. If you click on the checkmark icon on the top right corner of the form, you will see that Power Apps has added a line of code to its OnSelect property:

SubmitForm(EditForm1)

This one line of code is all you need to save the data back to the table! This is amazingly simple! This function works well if you have a primary key on the table. You can add more advanced code for error handling and other cases where you want to update many records at once (See Patch), but this one line of code is sufficient when you are working with one table with a primary key.

Once the data is saved, we want the app to refresh the Power BI report and the app to navigate to the first screen. Modify the OnSelect property to look like this.

SubmitForm(EditForm1);PowerBIIntegration.Refresh();Navigate(Screen1);

PowerBIIntegration.Refresh() is what refreshes the page. (If you get red squiggly lines under this function, you are most likely editing an old app that cannot refresh the page.)

Navigate is a is very simple function and self explanatory. You can read more about it here. It lets you move between screens.

The last thing left to do on this screen is to set the OnSelect property of the X icon to let the users cancel out of the edit form if they change their mind. Set the OnSelect property of the X icon to Navigate(Screen1).

This finishes our work on the second screen. The last step left is to add a way for users to navigate from Screen 1 to Screen2.

On Screen1, select the gallery. Click no the “>” icon on the first item in the gallery. Set its OnSelect property to Navigate(Screen2).

To make the first screen look nicer, you can add a header to it. The easiest way to do this is to select the header from Screen2 and copy it over to screen 1 and change its text to “Sales Orders”.

You are almost ready to use the app in your Power BI report. You must first save the app and publish it. Go to File and choose Save and follow the instructions.

The creator of the app gets access to the app by default. If you want other users to use the app, you must give them access by sharing it.

Now if you go back to your Power BI report, the Power Apps visual will load your app. You report will look like this:

Select the first item from the Gallery and click on the “>” icon to go to the Edit screen. Change its ProductSubCategory to “Test Bikes” and change its sales amount to 327800.27. Click on the checkmark icon to save the changes.

Your report will update to reflect the changes. You will have a new bar in the chart for “Test Bikes” and the SalesAmount shown in the card updates to $4M.

This is a very useful feature for users who want to analyze data and update data all from Power BI.

Future app changes

As long as you keep the Power BI report and the Power Apps windows open, you can go back and forth between them and make changes to the app and see the changes back in the report once you save the app.

If you make a change to the app and don’t see your changes immediately in the Power Apps visual, save the report, close it and reopen it.

For all future edits to the app, you should start the process by editing the Power BI report and then editing the Power Apps visual to get to the Power Apps studio. This is required if you are adding new fields to the app. Additionally, this way you will have data in the Power Apps studio while you work on the app.

Limitations

The Power Apps visual has some limitations.

• In order to add new fields to the app, the app must be edited from Power BI Service after changes to the Power BI report fields.
• Can’t trigger a refresh of Power BI reports from Power BI Desktop.
• Can’t filter a Power BI report. It receives context from a report but cannot send context back.
• Not supported in Power BI Report Server.

Always Publish the report to PBI Service first.

Summary

The order of operations matter to get the automatic page refresh to work:

The following shows the different scenarios I tested with the results:

Report Created In Power BI Desktop	Report Published and  Edited In Service	New App from Power BI Service	Existing Power App Not Created from Power Apps visual	Existing Power APP Created from Power Apps visual	Page Refresh Works?
Yes	No	–	–	–	No
Yes	Yes	Yes			Yes
Yes	Yes	–	Yes	–	No
Yes	Yes	–	–	Yes	Yes

To summarize:

• You must first publish the report to the service, open the report from the web interface and edit it.
• Add the Power Apps visual.
• Select fields to add them to the visual.
• Follow the visual’s instructions.
• Save and share the App.

Download Sample Code

I have uploaded the Power BI report and app that I used for this post to my GitHub repository. Once you open the reports, you must go to Edit Queries –> Data Source settings or follow the prompts if you click on Apply Changes to point the reports to your database. Similarly you have to connect the Power Apps sample app to your database for it to start working. Go to Screen2 and click on the Connect url on the form and follow the instructions.

Useful links and videos

• Microsoft Help
• Guy in a Cube: Patrick Lablanc
• Prathy Kamasani and Charles Setrling
• Charles Sterling