Updating Data In SQL Server With Power Query

As of the November update of Power Query, it’s now possible to write your own SQL query when you’re moving data out of SQL Server into Excel using Power Query. So I got thinking… if you can write your own SQL query, can you execute any other SQL statement? Can you use Power Query to move data out of Excel and into SQL Server? Well, it turns out you can… with some limitations. This blog post details what I found out while researching this problem.

I started with a simple table in a SQL Server database with two columns, Fruit and Sales, and some data:


I then created a new function in Power Query with the following definition:


    UpdateFunction = (Fruit, Sales) => 




                            [Query="UPDATE [FruitSales] SET [Sales]=" & Number.ToText(Sales) 

                            & " WHERE Fruit='" & Fruit & "'"])




As you can see, it takes the name of a fruit and a sales value and updates the appropriate row in the SQL Server table. I then created a new table in Excel with some new fruit sales values:


Used this table as the source for another Power Query query, and for each row in this table called the function above:


    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    InsertedCustom = Table.AddColumn(Source, "Custom", each UpdateFunction([Fruit],[Sales])),

    #"Expand Custom" = Table.ExpandTableColumn(InsertedCustom, "Custom"

                        , {"Records Affected"}, {"Custom.Records Affected"})


    #"Expand Custom"


I ran this query, and lo! My table in SQL Server was updated:


There are some interesting things to note here though. First, for each row in my Excel table, and each time an UPDATE statement was run, Power Query showed a prompt warning me that it was about to make a change to my database:


Probably the safe thing to do here, I think.

Furthermore, running a Profiler trace showed that each UPDATE statement was run at least twice. In fact, I originally started my tests with an INSERT INTO rather than an UPDATE, and found that since the INSERT INTO was run multiple times I ended up with duplicate rows in my table.

None of the code I’ve showed here should be used in a real application of course, but with some thought (and maybe a few changes to the way Power Query behaves), in the future it might be possible to use Power Query to move data out of Excel as well as in.

22 thoughts on “Updating Data In SQL Server With Power Query

  1. Hi,

    just played with inserting an EXECUTE Statement after an innocuous SELECT and it worked, but not without requiring me to confirm that I really want to do this, and I can only do it from EDIT mode.
    The purpose is to execute a stored procedure which in turn submits and starts an SSIS package loading the table just prepared by some other PowerQuery query into the database, the Advantage being that you can all do it from Excel and need no SSIS component whatsoever on the Client. I wish this could be done in a “legal” way.

  2. Maybe this is an old topic but does anyone know why the insert statement runs multiple times? I’ve tried insert directly and then it runs about three times per row and when I do the same thing through power query as a stored procedure i get the following results: if i send one row I get one row inserted; if I send two rows I get four rows inserted and if I send three rows I get nine rows inserted. This far i looks like I get x (number of rows that I want to send to the target-table) ^2 rows in my actual target table.

    If anyone has any theories or can help me with this it would be really appreciated.

    • Hi Kristian, I suspect that the reason is that PQ is trying to validate the query before it runs. In any case, I believe that using PQ to change data in a database (rather than to import data from that database) is not something the PQ Dev team encourage; it may not even be officially supported.

      • Maybe you’re right. However, I managed to make it work with only one insert per row. The solution (don’t really know why this works) was to empty the “receiving” table. I took you example from above and when I emptied the table showing the results before I ran the statement it made only one insert. But if I had e.g. three old result-rows showing in the table it made one full (the number of rows I wanted to insert) insert per row in the resulting table. So now I “clean” the result-table after every insert and it works rather well.

  3. Is it just me, or does updating data in SQL seems like a really obvious use case for PQ?
    I always have power users who want to master data in Excel, and use that to update tables in SQL. I really don’t have a good answer for this at the moment; I know you can mash up MDS to achieve this, or have SSIS import a spreadsheet, but both of those have a lot of moving parts, and are a bit messy.
    If PQ could target a SQL table, and present a nice UI, that was effectively backed by an UPSERT or MERGE statement, I’d use that all the time.

    • Yes, I think it would be useful for Power Query to be able to update data in SQL Server, but I can also understand that it would be very hard to get right and I think the dev team are right to focus on their primary use case of getting data into Excel right now. Maybe in the future though…

  4. Thanks for the post Chris! I’m trying this technique out to write package weights back to a SQL Server custom table from Excel. Our ERP tracks gross weight at the package level, but the biz wanted to enter net weights at the item level. The users only have SQL Server write access on the specific custom table for tracking these net weights so the risk of bad code is pretty minimal.

    I also found that you can turn off those annoying warnings by unchecking the box in this screenshot:

  5. New to power query, I need help with query
    UpdateFunction = (PersonID,LastName,FirstName,City) =>
    [Query=”UPDATE [PersonID,LastName,FirstName,City] SET [City]=” & Number.ToText(City)
    ] )

  6. Thank Chris for your prompt reply. I copied your script and modified according to my sql table.
    When I execute the query . I get one row that displays the query syntax and it doesnt update the table
    UpdateFunction = (PersonID,LastName,FirstName,City) =>
    [Query=”UPDATE [PersonID,LastName,FirstName,City] SET [City]=” & Number.ToText(City)

    Here goes the steps that I follow.
    1. Retrieve the data from sql into excel via Power query
    2. Create the function in blank query.
    3. Execute the query..

    Did i miss anything? Thanks for your help

  7. I couldn’t trace the transaction in sql. it looks like nothing happen in sql when run the queries from power query. is the query correct ?

    • The query below defines a function. Are you calling this function anywhere else in your code, or is this the only query in your workbook? You will need to call the function somewhere for Power Query to execute any SQL – you can test it by clicking the Invoke button.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s