Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Updating Data In SQL Server With Power Query

with 4 comments

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:

image

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

let

    UpdateFunction = (Fruit, Sales) => 

                        Sql.Database(

                            "MySQLServerInstance", 

                            "PowerQueryTest", 

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

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

in

    UpdateFunction

 

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:

image

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

let

    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"})

in

    #"Expand Custom"

 

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

image

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:

image 

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.

Written by Chris Webb

December 9, 2013 at 9:26 pm

Posted in Power Query

4 Responses

Subscribe to comments with RSS.

  1. Maybe Power Query should be renamed Power DataMover&Shaker.

    David Hager (@dhExcel)

    December 9, 2013 at 11:03 pm

  2. Hat dies auf MS Excel | Power Pivot | DAX rebloggt und kommentierte:
    Chris Webb beschreibt in diesem Beitrag, dass es mit Power Query theoretisch möglich ist, Daten im SQL-Server Backend zu aktualisieren. Ist sicher nicht gerade das, was sich die meisten DBA’s wünschen…

    selfservicebi

    December 11, 2013 at 1:15 pm

  3. Interesting stuff I m pretty sure there are it sections out there which would want to disable this

    Dalkeith

    December 28, 2013 at 9:55 am

  4. 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.

    Gottfried Eder

    June 20, 2014 at 5:55 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,081 other followers

%d bloggers like this: