Exporting Power Query tables to SQL Server










Changing the scale















9 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


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


  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 🙂


  3. I wonder if there is a way to use a text odbc driver to write the query output as a csv. Ideally in 10k or so chunks. Then you could define your transformation jobs as excel embedded pq’s and write a simple python program to schedule, run and load csv to destinations in a more reliable etlish way.


    1. I’ve tried this approach and it has limitations. At least while trying to insert data into a file via OleDB route. The file has to exist. Value.NativeQuery doesn’t support the Microsoft.ACE.OLEDB.16.0 driver. And the list of problems goes on. If you want to play around with this approach have a go at the following command

      = OleDb.Query(“Data Source=C:\code;Provider=Microsoft.ACE.OLEDB.16.0;Extended Properties=””Text;HDR=No;”””,”INSERT INTO [abc.csv] VALUES (1)”)

      The file abc.csv has to exist beforehand. And also you need to instantiate it with a newline (header).

      In my opinion a more promising approach if you want to insert data into something and later read it from a file would be to use SQL’s FileTables


  4. Just in case there are any more PowerBI newbies like me out there, a note that had me stumped for a while: The final example uses “Json.ValueFrom” which will come up as a syntax error. It doesn’t exist. Spent some time googling and found nothing. After some trial and error I hit upon the answer… it should read “Json.FromValue”.

    Liked by 1 person

Leave a Reply


WordPress.com Logo


Google photo


Twitter picture


Facebook photo


Connecting to %s