Write Back to SQL Database from Power BI

Did you know that Power BI can be used to write data back to a SQL database? It’s actually really useful once you set it all up. In this tutorial, I will show you how to pull data from a web API and insert the results into a SQL database. We can even report off the freshest data directly in our Power BI report! I will list all of the necessary steps below, but make sure to watch the video tutorial above to understand the full solution. Let’s get started!

The web API that I am using is unimportant for the purpose of this method, but just for your knowledge, I am querying my YouTube channel stats to get up-to-date view information each hour. I set up this example a few days ago to ensure I had enough data for this demo.

At the time of writing this post, I have 76 rows of data representing 76 hourly data pulls. Here’s a quick visualization to nail down the point:

So how did I get all of this data into a SQL database using only Power BI? Let’s take a look at the key steps. In Power Query, I have two queries, named #”API Call and INSERT Code” and #”Run SQL and Pull Data”. Starting with the first query, let’s go through the essential steps to write to the database.

Step #1: Pull Data from Web API or Desired Data Source

All of my steps through the “RawData” step are simply me trying to return the correct data and format it properly for how I want to store it in the database. Your steps will look differently depending on your data.

Step #2: Add Custom Column Resembling SQL INSERT Code

In the “AddSQLColumn” step, I am simply creating a custom column and entering in the following code:

 "IF EXISTS (SELECT * FROM HourlyStats WITH (UPDLOCK) WHERE CreatedDateTime = '" & Text.From([CreatedDateTime]) & "')

SELECT * FROM HourlyStats

ELSE INSERT INTO HourlyStats (CreatedDateTime,Views,Subscribers,Videos) VALUES ('" & Text.From([CreatedDateTime]) & "'," & Text.From([Views]) & "," & Text.From([Subscribers]) & "," & Text.From([Videos]) & ")

SELECT * FROM HourlyStats"

This may look like a lot of confusing text and M, but it’s actually just a lot of concatenated strings to eventually return a valid SQL INSERT statement. I’ve separated the statement into four sections to aid understanding. The first section checks if the record already exists in the database before inserting. This is EXTREMELY important because often Power Query will run queries multiple times during refresh, so you definitely want to set up some checks to make sure the data is not being inserted multiple times.

If the first section is true, we simply want to SELECT * FROM HourlyStats meaning we are not performing any INSERT. If the record doesn’t already exist in the database, we want to perform and INSERT with our current data. This section looks a bit messy since you have to wrap each value in a Text.From() function to return the text representations of the data to be able to concatenate them as a string, but it’s not too bad once you play around with it. The key is to pay close attention to your quotation marks and commas. An example return for this section looks like:

ELSE INSERT INTO HourlyStats (CreatedDateTime,Views,Subscribers,Videos) VALUES (‘2/10/2020 4:00:00 PM’,1321924,16100,120)

Finally, we want to SELECT * FROM HourlyStats in order to return all of the data after the INSERT statement has (or hasn’t) been performed. This will allow us to write the data to the database and then immediately report off of it.

Step #3:  Drill-down into the SQL Column

In order to turn our new column into a text string, simply right click the value and select “Drill Down.”

You will be left with a single text string, as such:

Step #4: Write the Data to SQL!

And now for the easiest part. Our second query is only comprised of two steps, and looks as such:

In the Source step, I am making a connection to my SQL database via the server name and database name. You may recognize this code as it’s what is created when you connect to a SQL database via the Get Data GUI. The second step is where the magic happens. The INSERT step called the Value.NativeQuery() function which requires a target and a query to run against that target. Since we are specifying our SQL database connection as our source and our SQL INSERT string as our query, we are basically saying “Run the result of our other query (INSERT statement) on our SQL database specified above.” And that’s really it! You’ll either have to test that this works for yourself or watch the video tutorial where I showcase a live example.

With this method, you are able to timestamp your data within Power BI and write back to your data source within the refresh process. You can take it one step further and publish a working solution up to Power BI Service 3 times, so you can refresh the data source once per hour! You will be able to achieve hourly data updates. You can even publish 6 solutions if you want to push data to SQL every 30 minutes 😉

I hope you enjoyed this neat trick! If you did, make sure you check out the BI Elite Training portal to really hone your Power BI, DAX, Power Query, and other Power Platform skills. There’s a lot of great stuff over there for far less than market-value, such as my Power BI Beginner Course. Hope to see you there!