Power App and Power BI Integration With Write Back and Reporting

We walk through the steps of using write back and integrated reporting with Power Apps and Power BI in this blog.

Microsoft’s Power BI is a powerful platform that facilitates enterprise business intelligence (BI) with scalable, self-service tools. With Power BI, users can connect to and visualize data they can use to create attractive interactive visuals.

By integrating Power Apps — Microsoft’s native app — with Power BI, users can write back data. Power Apps will display and allow users to interact with data from the Power BI report, including creating, editing and deleting data rows that update in real time.

For example, we recently had a client that wanted to see the Power BI report along with an app to update, insert and delete the data in the source or edit in line and report from one canvas. Before, users were not able to do so from Power BI.

However, Power BI doesn’t currently have a native solution for inline or bulk data updates while interacting with a report or dashboard. That means to push changes to data, users must make updates directly in their data stores and then, if they’re not using Direct Query (live mode), refresh a data set to complete the process flow. It’s important to note that if Power BI is in Direct Query, users can see visuals updated in real-time.

Either way, the process can be inefficient and pose problems for users who don’t have access to a specific back end or the underlying data. Fortunately, there is a better way to do this using write back.

Outlined below, the solution for the data write-back function provides an interactive and efficient way to change your data source directly from Power BI.

Write Back to SQL in Power BI

Microsoft Power Apps is a key pillar of Power Platform, which provides the write back to Power BI reports using Power apps Visual. It helps write the data back to SQL or any other data source used within their respective projects. This allows a Direct Query connection to Power BI and enables data visualization with the Power Apps live in the Power BI report. To do so, follow these steps:

1. Create a Power BI report in Direct Query mode using interactive visuals per the business needs. Get data in Power BI and use tables or SQL Scripts.
2. For the write back function, use Power Apps Visual within Power BI.
3. Drag all required fields you need to update to the Power App visual.
4. The visual will ask you to create an app within make.powerapps.com or choose from an existing app, as shown below:

5. Upon clicking Create new, users are directed to the Power Apps page. Power Apps will automatically create an integration object using the selected fields passed from the report.

Editing and Deploying Apps in Power Apps

In Power Apps, users can create and edit forms based on specific requirements using various available options. You have to embed a Power App in your Power BI report to write back to the data source. The tree view shows the visual hierarchy of the components.

Since you have already established the data connection, Power BI uses the forms option to update the record in the database. Select and arrange all the required fields. Edit fields by selecting Fields > Edit Fields at the right corner, as shown below.

The use case may require making UI/UX changes to the app, such as changing the header color or other design layouts. The above form also includes a Submit button at the end with the ON Select expression as SubmitForm (Form Name). Click Save to save the app. Microsoft then creates the app on the backend and adds it to the Power BI report, as shown below.

Deploy your final Power BI report to service with interactive visuals and the app created. Shown below is the report published with the Power App embedded as described.

Drilling into the Data Source and Architecture

Let’s take a closer look at the data source and architecture. The data source is MS SQL Server. However, the approach would be the same for any other source, such as SharePoint, Dataverse, Synapse and so on.

The Power App will instantaneously refresh the Power BI report and provide a virtually unlimited refresh scenario. The Power BI report will use MS SQL Server with Direct Query mode and not rely on scheduled refreshes or imports.

A major component of this solution is the ability to pass data from Power BI into Power Apps for updates. It is critical to refresh the Power BI dataset to ensure all users can see the updates. Here are the steps:

1. Get data in Power BI by importing views and tables, using Direct Query or writing custom SQL queries
2. Create a Power BI interactive report and integrate Power BI and Power Apps via the Power Apps visualization in the Power BI desktop application.
3. Use Power Apps to create a canvas app, which will provide the ability to interact with and update all necessary data.
4. Test and publish your solution to Power BI Service.

Other Potential Use Cases

In addition to the examples above, other use cases could include:

• Editable Grid Power App: This app will give users an Excel look and feel. Users can delete, update and insert any data and see the report changing in real time.
• Approval Workflows: Extending the capabilities of Power BI with Power Apps and Power Automate allows end users to collect data requiring review directly from a dashboard and send data to subsequent approvers.

You need the relevant Power Platform licenses to run this solution in production. To use Power BI along with Canvas Power Apps, you must have separate licenses for Power BI and Power Apps.

There are some limitations to this application. For example, in import mode, only the report will reflect the changes. You need to manually reset the dataset or schedule a refresh. Additionally, in Direct Query write back works like import, and you can see the results immediately without a refresh on the Power BI dataset.

Finally, there is another way to add a Power BI report to the Power App inversely to what we discussed above and with the same use case. The only difference in steps would be to create and publish a Power BI report using a Power App and then add the Power BI report. Select Insert > Chart > Power BI Tile and use the embed link for the website or portal from the service to do so.

Conclusion

Write back enables end users to update values in their data warehouse directly from the Power BI report and provides a rich user experience. In this blog, we demonstrated how Power Apps allows us to write back data to respective source from Power BI report and then do reporting and analysis on top of updated data.