Blogs / Microsoft / Power BI / Write-back into SQL Database using Power BI with Power Apps

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

Sep 3, 2021

SHARE

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.

Creating sample data in SQL

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.

Power Apps visual in Power BI

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.

User Interface of Power Apps

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.

Edited form in Power Apps.

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.

Adding a new record to the source file

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.

The refreshed dataset

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

The dataset in SQL also gets updated

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


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!