Power BI write back to SQL Database: 2 methods

Written by Andy McDonald
Reviewed by Johannes Ã…kesson
Jan 03, 2025
7 min read

Power BI dashboards and reports often pull data from a SQL Server database, but what happens when you need to update that data? For businesses, having Power BI write-back to SQL functionality is critical for staying agile. In this article, we’ll explore two methods for writing back to SQL, including the SQL Spreads Excel Add-In, a user-friendly solution designed specifically for business users familiar with Excel.

Power BI write-back to SQL: Use cases

While dashboards and reports are typically read-only, there are situations where your business users need to make changes to the data that they are viewing in a Power BI report. Here are a few common use cases:

  • Adding comments to records  â€“ For example, your sales team might need to add context to sales figures and save these notes to the database.
  • Providing status updates – Project managers may need to update the status of tasks in a Power BI project status report.
  • Updating forecast values – Finance teams often require managers to update sales targets or cost budgets displayed in a dashboard, ensuring forecasts stay accurate as conditions change.

Let’s consider the last use case: updating forecast values. We’ll explore 2 methods on how business users can update the forecast information as conditions change without having to make changes in SQL Server directly.

These are the two methods in this article:

Need business users to update forecast values in Power BI reports? Download a free trial of SQL Spreads Excel Add-In to start getting forecast data into SQL Server for accurate Power BI dashboards.

 

Try it for free

 Power BI write-back to SQL using SQL Spreads

The first option is a simple solution requiring no coding skills and using the familiarity of Excel as a front end. The SQL Spreads Add-In for Excel can make direct updates to SQL Server giving business users a really easy way of updating their Power BI dashboards immediately.

Why is it easier to use SQL Spreads?

Four main benefits for using SQL Spreads Excel Add-In:

  • Quick and easy to setup. Within minutes, you can setup a solution and share with business users.
  • A familiar Excel interface. Business users love Excel. So stick with what they know.
  • Distinct Designer vs Data Editor roles. With SQL Spreads, Designers set the rules, and Data Editors stick to just the data they need to update. No risk, just guided edits.
  • Advanced features such as Look-ups and Tree Filters are helpful with large datasets.
Diagram of how the Power BI write back to SQL Server works with SQL Spreads

In this solution, business users will view the Power BI reports/dashboards as normal, and make updates to the forecast data in Excel (through the SQL Spreads Add-In).

To demonstrate how this works, we’re going to look at a simple example with the following components:

  • Data source: we have a sample SQL database that contains a Forecast table and an Actual table. The data in the actual table is monthly summary data pulled from a full sales database, whilst the forecast data is going to be updated from the SQL Spreads Excel Add-In.
Sales database Forecast table
  • Power BI Report: the Power BI report contains some simple visualizations for Target Sales and Budget Costs.
Example of a Power BI forecasting report

Step (1): Get the data

The data we’re going to use is in 3 tables (Actual, Forecast, Calendar) in a SQL Server database.  Each table has a primary key.

Step (2): Create the basic Power BI report

For this example, we’ve just created 2 line/column charts and a date slicer.  These show the actual vs target sales data and actual costs vs budget costs.  For information on creating basic reports, check out our previous article Creating reports with Power BI, SQL and Excel.

Step (3): Connect Excel to the database in SQL Server

With the SQL Spreads Add-in installed in Excel, we can connect to the Forecast table in our database and view, add or edit data in that table from within Excel.

Click on the SQL Spreads ribbon menu tab and then click on Open Designer

Click on the Open Designer button in the SQL Spreads ribbon to open it up

Next, click on the Edit connection button in the SQL Spreads Designer pane and provide the details to connect to your SQL Server (more information is in our knowledgebase article on How to connect to SQL Server).

Connect Excel to SQL Server by clicking on the Edit button in the documents SQL Server connection

Once SQL Spreads is connected to SQL Server, you can then select the table you want to manage from the SQL Spreads Designer panel on the right hand side – in our example it is the Forecast table.  As soon as you select the table, the data is retrieved from SQL Server and displayed as a table in Excel.

Forecasting table loaded into Excel using SQL Spreads Designer

When you finished making changes, simply click on the ‘Save to Database’ button in the SQL Spreads ribbon and the data will be written back to SQL Server.

You can now make changes to the forecast data or add new rows.

The data changes will reflect in the Power BI report the next time there is a scheduled refresh or manual refresh.

Did you see how easy that was to setup SQL Spreads to update SQL Server? Quick, easy, and well on your way, that’s the power of SQL Spreads.

Power BI write-back to SQL using Power Apps

The second method uses a special visualization type in Power BI – the Power Apps for Power BI visualization.  When you add this visualization to a Power BI report, you are essentially embedding a Power App app into the report.  This means that you can create edit forms that allow users to make updates to the underlying data source directly from Power BI.

Power BI Power Apps SQL Diagram

In this solution, business users will view the Power BI reports/dashboards as normal, and make updates to the forecast data in the Power Apps edit form.

We will use the same example Forecast data and Power BI Report as the previous method. With the exception that this Power BI report contains simple visualizations as well as the Power Apps edit form which allows users to make updates.

Power BI Report showing Power Apps visualization

Step (1): Get the data

The data we’re going to use is in 3 tables (Actual, Forecast, Calendar) in a SQL database as per the previous example.

Step (2): Create the basic Power BI report

Again, the process of creating the basic Power BI report is the same as the previous method. 

Step (3): Add the Power Apps for Power BI visualization

When you add the Power Apps for Power BI visualization to a report, the following information is displayed in the visualization which explains the steps to take.

Power Apps for Power BI visualization steps

For this example, we’re going to add the following data fields from the Forecast table to the new visualization:

  • DateKey
  • Budget COGS, Budget Capex, Budget Opex
  • Target Sales

Once these have been added, the Power App visualization will prompt you to select an existing app or create a new one.  We’re going to create a new one, which opens a new canvas app in the Power Apps studio.

Battling to get your Power Apps solution up and running? Rather use SQL Spreads. It’s so easy to use, you could almost do it with your eyes closed.

 

Try it for free

Step (4): Customize the app in Power Apps studio

When you click on ‘Create New App’, an app is created with a single page containing a data gallery.  We’re just going to add a couple of extra controls to make this usable in our report:

  • Edit form – this is so that users can edit/add data
  • Submit button – this will save the changes in the form
Power Apps Customize App

Once we’re happy with the layout of the screen, we can save and publish the app and see it updated in our Power BI report.

Power BI Power Apps Example Final Report

With the edit form in place, users can now update the data in the Forecast table in SQL Server from the Power BI report.

Notes/limitations with the Power Apps for Power BI visualization

It’s important to consider the following when looking at using the Power Apps for Power BI visualization in your reporting solution:

  • The maximum number of records that can be passed from Power BI to Power Apps visual using the “PowerBIIntegration” object is limited to 1000.
  • The Power Apps visual can’t trigger a refresh of Power BI reports and Power BI data sources from within Power BI Desktop. In other words, when you edit and save data in the Power Apps edit form in our report example, the data write back from the app to SQL Server will only occur on the next scheduled refresh (or manual refresh).
  • The Power Apps visual can’t filter the data or send any data back to the report.
  • When you add the Power Apps visual to your report in Power BI Desktop, you should publish the report and continue the app creation and edit process from the Power BI Service UI.
  • It isn’t easy to display and update large datasets in Power Apps.

Stop Wasting Time – Use SQL Spreads

In this article, we’ve demonstrated two methods for enabling writeback to SQL Server from Power BI.

The first method, using SQL Spreads, is by far the easier and faster solution. With minimal setup, you can leverage the familiar Excel interface, empowering your business users to update SQL Server data themselves. SQL Spreads handles large datasets easily, making it a good choice for businesses managing significant volumes of data.

The second method, integrating Power Apps into a Power BI report, can also achieve writeback functionality. While it provides a visually appealing solution, it involves a steep learning curve and significant setup time. Additionally, Power Apps has inherent limitations, such as its 1,000-record data cap, which can restrict its effectiveness for larger datasets.

Instead of spending hours or days grappling with Power Apps, consider SQL Spreads for its simplicity, scalability, and ease of use. SQL Spreads offers a solution that’s quick to implement and perfect for businesses needing an efficient way to manage their data.

Ready to simplify your Power BI writeback? Visit SQL Spreads to download your free trial today!

Get started today. Download a free trial of SQL Spreads and get business users involved in updating SQL Server data now.

 

Try it for free

Related Articles

Try SQL Spreads for free

Try SQL Spreads during 14 days for free. The free trial is the fully
functional, time-limited only product.
Try Excel Add-In for Free download-image
stars-image
14-days Free Trial. No credit card required.