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!
Parker Stevens
Parker is the creator of the BI Elite YouTube channel, a community of over 30,000 students learning Power BI, DAX, and Power Query. He is a Microsoft Data Platform MVP for his work with Power BI and continues to provide elite Power Platform training courses to help data analysts, BI developers, and citizen dashboard creators reach their goals. Parker is committed to producing high-quality training content that is also extremely cost-effective, to ensure that the largest amount of users can benefit from the content. Training courses located at https://training.bielite.com/
18 Comments
Leave a ReplyCancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
This a great hack by using Value.NativeQuery(). However, I do not like the thought of the possibility of executing millions of INSERT statements if someone accidentally apply the trick to a fact table with millions of rows. 🙂
Definitely keep it small-scale with this trick 😅
Hi Parker,
Awesome idea. I tried it out but my data just updates one record in sql server db. The SQL step in power bi query editor always shows the values for the row where I clicked Drill Down.
My data source is exchange rate publish by central bank and they do one daily update.
Your trick is very useful but I am wondering about writing back multiple records (like you might do with bulk import).
I am accessing a cloud sql database and trying to create a paginated report WITHOUT using a capacity workspace ($5k/month is too steep for our firm). So, using something like your technique, I want to read from the cloud db in power bi, then write records back to a local sql db, then use ssrs to create a paginated report from the local data.
I can use your technique to write one record at a time but since there will be multiple tables and thousands of records, it will probably be pretty slow.
Any feedback would be appreciated.
Hi Tommy,
did you by any chance find a solution for multiple rows?
I d really appreciate a hint if there is any.
Best Regards
Hi how to write back in Power BI with existing data table?
HI, I’m having trouble making it work in Power BI Service. It is working in Desktop, even when I have Online Editor open, but when it has to do scheduled refresh it shows Refresh completed, but there is no change in database. I tried to implement it in Power BI Dataset and Dataflow.
Wondering if we can use the same process by creating a text box for end users to write financial commentary & it gets written back in my sql database. Can you please guide – how can i achieve that?
Hi Parker, would your technique help insert data into database through Power BI gateway?
@Tommy Glenn – Rather than using Application (e.g. Power BI), You should use ETL (Data Factory or simple SSIS). 🙂
@tommy Glenn: The Acterys Table Edit Power BI Visual in the visual marketplace: https://appsource.microsoft.com/en-US/product/power-bi-visuals/wa200001457?tab=overview provides you with comprehensive bulk edit functionality.
looks like using this, only one record can be written to the SQL.. not valid for multiple records
can you use select * into a newtable_name on the sql statement if i just want to copy all the data withoud typing out all the column name my email is duct@ichs,com
That was a great video — succinct, no missteps, informative, with the back up information in written form. Thank you.
The SQL insert only works in data preview mode, but not when the queries are refreshed in the visuals view. What am I doing wrong?
Hi Parker, I’m not able to make your process work. When you drill down a statement, ONLY that statement is executed by the RunSql query, how can you make the query load ALL the new records?
This is great ! does this work with update statements as well ?
This was very interesting! I am trying to get it to work with my use case, where I have a list of monthly exchange rates I am fetching through an API. This list goes a few years back in time and I’d like it to update daily and write back to my SQL database.
Using your method I am able to write back only one record at a time (taking the max Year-month value in my table: = #”Added Custom”{0}[SQL]). How do I get it to loop through all of my historic records and check whether or not they exist? 🙂