Exporting Power Query tables to SQL Server

Power Query is a great tool for data mashup, however when it comes to really show its powers as a desktop ETL tool, you realise that no matter how powerful at E an T it is, the L is falling behind, as you can only load the data into the embedded tabular data model that comes with Power BI.

Ignoring R, local cubes and HTTP POST requests, I wanted to showcase a few other methods for exporting data from Power Query and loading it to SQL Server.

The logic I follow is very simple — because there is no obvious limitation to the type of queries you can run against OleDb or ADO.NET datasets — a SELECT query will be executed with same success as an INSERT query.

As a prerequisite, on the SQL Server side I’ve created a table which I populate initially with single records, then with 1M records.

OleDb

Following the logic described above, inserting a single entry into Demo table via OleDb looks fairly trivial.

However if you try to follow this approach you’ll soon notice that in addition to fiddling around with Text.Format to get your INSERT statement righ, you will also have to approve each parameter variation from your SQL statement. Which makes this approach unfeasible unless you construct your INSERT statement to include all of the millions of records in a single string.

ADO.NET

On the other hand an ADO.NET connection wrapped in a Value.NativeQuery function provides a more familiar approach to performing data INSERT. This method has the convenience of passing query parameters as a record or list inside the SQL statement. As a result you need to approve the firewall rule for the SQL query only once.

Changing the scale

The methods I’ve described so far work reasonably well for times when you need to insert a few hundred records in a SQL table. However when the problem explodes to millions of rows, you need to do some prep work before sending over all of that data. I’ll detail 3 techniques for exporting datasets with millions of records: Table Valued Parameters, XML and JSON.

As an example I use a 1M records table I generate from a list with an incremental sequence Key and GUID generated Value field. Table dummy.

TVP

When doing bulk inserts in ADO.NET, the most recommended approach is to use a Table Valued Parameter to send data to a Stored Procedure which performs the INSERT. As a prerequisite on SQL Server side I had to add a table type and a stored procedure that accepts this table type as a READONLY parameter.

Although sending a TVP into an SP is a straightforward task in .NET, it requires casting the data reader (table) to a SqlDbType.Structured data type. 

I wanted to rely on Power Query internal data type conversion and sent a table as a parameter to my newly created SP. Needless to say I was greeted with the error below


After giving up on the idea of casting a Power Query table into a SQL Structured data type, I’ve decided to replicate the internal call during of an  exec SP with @TVP. Thus I’ve built a rather lengthy 1002 row SQL statement that declares an internal table, inserts all the records from the dummy table and calls the SP with internal table as a parameter.

XML

Back in the days when XML was still cool, sending some tabular data to SQL Server was the matter of serializing a recordset into an XML document and sending it to a stored procedure that parsed it back into a recordset. Nothing has changed since on the SQL Server side.

Since Power Query lacks any functions for constructing XML documents, I found it very difficult to come up with an approach for building binary XML documents that performs on par to JSON serialization. I’ve tried various combinations of looping through the records of a table via List.Generate and List.Accumulate, nevertheless Table.TransformRows and avoiding making any calls to Text.Format seemed to deliver best performance.

On calling Value.NativeQuery, Power Query converts the binary string into SQL varbinary(max) data type, thus for the final call it requires only mapping the placeholder to the binary XML document.

JSON 

Since SQL Server 2016 introduction of JSON support, sending and receiving data to/from SQL Server got a lot easier. Power BI is no exception, sending data to a SQL Server table requires addition of a SP with JSON parameter and on Power Query side serializing the dataset as a text bases JSON object with Json.FomValue.

Because of native JSON support on both SQL and Power BI sides, this was by far the most easy and fast way of sending data across.

5 thoughts on “Exporting Power Query tables to SQL Server

    1. Although using PowerBIETL is a valid choice, I don’t like the approach as it relies on PowerShell. My purpose for this post was to demo how Power Query can be used for data extraction as well as data ingestion to other sources than the $Embedded$ cube

      Like

  1. Thank you for this. When I replicate your examples, sometimes the table gets inserted twice (2m rows). Any idea how to prevent this?

    Like

  2. Hi Matt, that’s a very good question.
    You are getting the duplicate records because Power Query executes the M script twice, one for preview, assuming that you preview the results of the script and once when it loads the query into the data model.
    You can solve this problem in a couple of ways. One would be to put the appropriate insert logic on the SQL side. One such mechanism could be to check on insert if the records are already present or not. Or have a flag which controls if the records get inserted or not. This could be set for example only after you’ve clicked apply and close in the Power Query Editor and refreshed your model.
    Another approach would be to run the script in the preview window of Power BI, and then disable the query.
    A more convoluted approach would be to set an “Insert Parameter” as a slicer in Power BI Report view. Then have another Power Query script that reads the slicer selection and sets a Power Query parameter that controls insertion of data.
    …And the list goes on 🙂

    Like

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