Write-back into SQL Database using Power BI with Power Apps

Power BI is extensively used to connect to SQL databases to create interactive reports and appealing visualizations. But, ever wondered how to update the data into the database directly from the report?

The write-back function in Power Apps is available to accommodate this. Write-back enables end users to change and update the values in the data warehouse directly from the Power BI report. In addition, write-back helps BI professionals to have a rich user experience in deploying solutions for planning, budgeting, forecasting, and adding comments.

Let us discuss how to write back data into SQL from Power BI.

Creating sample data in SQL and sample Power BI report

To begin things, let us create a sample sales dataset. Here we have used five columns with S_no as a primary key. After creating the table in SQL, import it to Power BI and create a sample report out of it.

1. Create a table in the SQL database with sample sales data in it.

2. Create a sample report in Power BI using the dataset created in the SQL.

Sample report in Power BI with data consumed from SQL

Adding PowerApps to the report

Once we create a sample report, we can add the Power Apps visual to our Power BI report by following the steps below.

3. Click on the Power Apps visuals and select the required fields. Next, the visual prompts us to either create a new app or choose from an existing one. In our scenario, click on Create new.

4. On clicking Create new, it navigates to the Power Apps page. Power Apps will automatically create data integration with the selected fields that are passed from Power BI.

Editing forms in Power Apps

In Power Apps, we can create and edit our forms based on our requirements. To edit, we can choose from the wide range of available options. In addition, we can use the Tree view to understand the visual hierarchy of the components.

5. Since we have already established the data connection, to update the record in the database forms option is used. Select and arrange all the required fields. We can edit fields from Fields-> Edit Fields.

6. Here, we have added three different screens and added all required fields.

The three screens used in the Power Apps.

7. Click on Save to save the app.

8. Then the app created is added to the Power BI report.

Adding Power Apps in the Power BI report

Write-back using Power Apps

We can edit or update the values in the data source directly from the Power BI using Power Apps.

9. Click on the “+” icon to add additional details. We can click on any filed to edit the values in it.

10. Click on Submit (✔) to submit the data. We will be directed to the first screen, then click the refresh icon (). Clicking the refresh option in Power BI will reflect the recent changes made.

11. The same data gets updated in the SQL database as well.

Limitations:

To create Power Apps, we need a Power Apps license.
In import mode, the changes will be reflected in the report only, and then we need to refresh the dataset manually or by scheduled refresh.
Direct query enables write-back similar to import, and the results are immediately noticed without a refresh on the Power BI dataset.
The Primary Key field on the table is required to enable the write-back on direct query mode.

Write-back will come in handy in many situations. Some of the use case scenarios are timesheet approval, updating the target values of the performance. This blog demonstrates how Power Apps provides the write-back capability to Power BI reports and helps write the data back to SQL from user inputs.

Learn more about Microsoft Power BI services offerings from Visual BI solutions here