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:
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:
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:
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.
Maybe Power Query should be renamed Power DataMover&Shaker.
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…
Interesting stuff I m pretty sure there are it sections out there which would want to disable this
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.
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.
Very interesting – thanks for the information!
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…
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:
http://i.imgur.com/aRXEpQJ.png
New to power query, I need help with query
let
UpdateFunction = (PersonID,LastName,FirstName,City) =>
Sql.Database(
“GJUSA”,
“PowerQueryTest”,
[Query=”UPDATE [PersonID,LastName,FirstName,City] SET [City]=” & Number.ToText(City)
] )
in
UpdateFunction
When I execute this query ,i’m getting an error.
What’s the error?
Can you tell me exactly what the problem is?
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
‘let
UpdateFunction = (PersonID,LastName,FirstName,City) =>
Sql.Database(
“GJUSA”,
“PowerQueryTest”,
[Query=”UPDATE [PersonID,LastName,FirstName,City] SET [City]=” & Number.ToText(City)
])
in
UpdateFunction
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
Can you run a Profiler trace on the server to see what SQL is actually being run?
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.
Hi Chris. very interested in this
I would like to build a tool in Excel which is used purely an ETL application to shape and combine source data files using Power Query. However, I would then like to load the shaped data to specific tables housed in an Access DB (don’t have access to SQL at all)
One of the advantages of this setup is that completely segregates my data layer from my Excel based reporting tools
My reporting tools would then connect to the Access DB to get their data rather than having to the job of processing the data files themselves
I suppose put another way, it would be good to have the Power Query functionality available within Microsoft Access
I agree, it would be good to have Power Query functionality in Access and a lot of other places too. I’m not sure that trying to load data from Power Query in Excel will work all that well, although you might get it working well enough for your purposes.
Thanks Chris
How would you amend the function above if you were dealing with an Access table rather than SQL? What would the syntax be, nb I would just be overwriting my access tables each time (not accumulating data). If I can crack this bit ill be off and running
The alternative is that I have my Excel based ETL tool load it’s shaped data into its own data model then export these tables as csv data files which could be collected by the Access DB but this is pretty long winded and a bit of a hack. Would prefer a pseudo ‘load to Access DB’ approach
Another feature that I’ve thought would be useful in PowerPivot going forward would be the ability to generate reports/data extracts like you can do in Access