Integrate PowerApps in Power BI report to achieve write-back ability

Most of the clients love interactive Power BI reports and all the capabilities it brings for the end-users. But there are situations when the users want to have the write-back ability from the reporting platform. For example, I am using Microsoft’s sample report: Customer Profitability. Say, you are the CFO of the company and you are viewing this Customer Profitability report. It looks good but you want the ability to enter comments based on different filters from the Power BI report. In this post, I am going to unlock the steps to implement PowerApps integration into Power BI report.

Assumptions

• You have developed Power BI report.
• You have PowerApps account.
• You have installed On-prem Data Gateway.

1. Create Sample table to store the comments from the PowerApps

In order to store the comments from the PowerApps, you need a table. You can also use table/ column from your existing dataset. In this example, we are storing it in a simple table.

CREATE TABLE dbo.PowerAppComments
(
	ID	                INT IDENTITY(1,1) NOT NULL,
	ReportName		NVARCHAR(100),
	FilterColumnName1	NVARCHAR(100),
	FilterColumnValue1	NVARCHAR(100),
	FilterColumnName2	NVARCHAR(100),
	FilterColumnValue2	NVARCHAR(100),
	Comments		NVARCHAR(200),
	CreatedDate		DATETIME DEFAULT GETDATE(),
 CONSTRAINT [PK_PowerAppComments] PRIMARY KEY CLUSTERED 
(
	ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2. Prepare your Power BI report

I am assuming your report is already developed and working. You will just need to make some space in your report to integrate PowerApps visual. This is the page where your users will want to enter comments or feedback. Select the PowerApps visual and place it in the empty space.

In this example, we are going to need interaction with Industry and Product so we will be dropping only 2 columns in “PowerApps Data”.

It should provide you option to choose app or create a new app. As this is a new app please select “Create New”

This should open a pop-up “Open Browser”. Please click ok and it should take you to PowerApps portal.

3. Create New App in PowerApps

Once you are re-directed from Power BI, you should see below screen with a gallery on the browser. Click Skip. The Gallery contains the values from the columns you dropped in “PowerApps Data”. In addition, you should also see “PowerBIIntegration” in the left panel showing that this app is integrated to Power BI report using those 2 columns. Please remember this app can be integrated with any Power BI report that contains those columns.

Before designing the visuals, first click “Save” via File option at the top left or Ctrl + S. After this let’s add the database connection and add the connection to the App. On the left pane, click on “Data” and “Connection”.

Click on “New Connection” at the top and then create “SQL Server” connection.

It should open a pop-up to enter the database connection details. In this example, I have chosen “Windows Authentication” and selected a Gateway installed in my machine. The account used here for authentication should have read-write access. Once all the details are entered, click “Create”. Now you should be able to see your SQL Server connection under data connections.

Now let’s go back to our App. Click on Apps and click on the ellipsis and then edit of the saved app. On the left pane, click on the data icon.

Click “Add data” and select “SQL Server” as we have created the table in SQL Server database.

It should show you the option to add the SQL connection that you have added to the environment. Add the data connection to your app.

Now it’s time to create visuals for your app. Feel free to play around with different visual options. In this post, I have chosen a very simple visual to showcase the overall functionality. You can click on “Insert” at the top to choose different visual options. The main Gallery (i.e. Gallery1) can be used to filter other visuals and it can be kept invisible. I have made the main gallery invisible but using it to filter “Industry” and “Product” dropdown list. This way it will interact with Power BI slicer selections.

The dropdown for “Industry” and “Product” should look something like below

The “Submit” button should contain Power Fx “Patch” command in “OnSelect” option to insert data into the backend table. Below is the sample code.

Patch(PowerAppComments, Defaults(PowerAppComments),
{
    ReportName:         "Customer Profitability Sample Report"
    ,FilterColumnName1: lbl_Industry.Text
    ,FilterColumnValue1:drpdwn_Industry.Selected.Industry
    ,FilterColumnName2: lbl_Product.Text
    ,FilterColumnValue2:drpdwn_Product.Selected.Product
    ,Comments:          txtinp_Comments
    ,CreatedDate:       Now()
}); Refresh(PowerAppComments); Reload

In order to view the comments, you can user table or gallery visual. If you chosen table then make sure to select the table in the data source and the required fields using “Edit fields”

4. Testing

If everything is setup correctly, then your Power BI report should show your Apps visual that interacts with the report slicers and filters. In below report, I have selected “Federal-Civilian” as Industry. The App interacted with Power BI to show “Federal-Civilian” in the Industry’s dropdown list. In addition, it also cascaded to show the product options available for that industry in product’s dropdown i.e. Gladius in the App.

Now, let’s try to enter some comments for this filter option and see how it works. Once you have entered the comment you should be able to view the comments in the table visual and backend table. I would recommend to add refresh button in PowerApps visual to reload the data from the database table.

The database table should show data entered from the PowerApps.

Conclusion

Overall, Power Platform provides some really cool functionalities that are possible to be built quickly. Although you can implement these functionalities using Power Platform, PowerApps integration with Power BI has some caveat and challenges when you are looking for an enterprise wide adoption that requires functional DevOps and CI/ CD. If you have developed a dev version of PowerApps that is integrated with dev version of Power BI then there’s no way to automatically change and re-point Power BI prod version to PowerApps prod version. I think this is a big drawback that I believe MS will have some fix in the future releases.