Hits: 23724

Update 14. May 2019:
There is another approach using Power Query in SSIS:

Since the launch of Power Query and even more Power BI Desktop, there have always been questions about the possibility to export data to SQL Databases from Power Query or Power BI.

As of today this isn’t possible as neither Power Query nor Power BI Desktop offers a native way to do that. But it works with the implementation of “R”, at least in Power BI Desktop.

But before we dive into the topic, I have to admit that usually I write my blogs in German. So, please I apologize if you have trouble understanding my writing….. 


To achieve the goal of exporting data from PBI Desktop to a SQL Database, you have to install R-Studio. And needless to say PBI Desktop and a SQL Database. And please be sure to have installed the R library “RODBC”.

Let’s start

Let us start with a simple set of data by writing the following code:

Source = #table(
{"CustomerId", "FirstName", "LastName"},
{"Hello","WORLD", "1"},
{"G-Day","AUSTRALIA", "2"},
{"Gruezi","SWITZERLAND", "3"}
in Source

Not so high sophisticated but it works for demo purposes.

Then call the “R-Script” and type the following code:
conn <- odbcDriverConnect("driver=SQL Server; server=NBXYZ; Database=ExcelDemo")
sqlSave(conn, dataset, tablename="Customers",rownames=FALSE, safer=FALSE, append=TRUE)

That’s all! Zwinkerndes Smiley

What does the “R-Script”?

  • The first line “library(RODBC)” calls the library in question.
  • The second line “conn <- odbcDriverConnect(“driver=SQL Server; server=NBXYZ; Database=ExcelDemo”)” declares a variable “conn” and assigns the driver, the servername and the Database.
  • The third line does some error checking “odbcClearError(conn))
  • The fourth line “sqlSave(conn, dataset, tablename=”Customers”, rownames=FALSE, safer=FALSE, append=TRUE)” saves the data set to the SQL Database.
  • The last line “close(conn)” closes the connection.

Export Data to SQL

Final Thoughts

After searching for a while to achieve this, I stumbled upon the solution, when I wrote an article about exporting data from PBI Desktop to a .csv file. After finding the solution, it seems to be very simple….

I’m pretty sure that there are more solutions out there to export data to SQL, but until today I haven’t found an article about this topic.

Und für meine deutschsprachigen Freunde

Der Artikel beschreibt die Möglichkeit, Daten aus Power BI Desktop in eine SQL Datenbank zu exportieren. Alles was Ihr dazu braucht ist der Power BI Desktop, R-Studio und natürlich eine SQL Datenbank.
Im Weiteren habe ich ein einfaches Datenset erstellt und dieses mit dem wirklich trivialen R-Code in die Datenbank exportiert. Das war schon alles.

Happy Querying!

9 thoughts on “Export data to SQL

  • 18. June 2018 at 0:27

    Hello Hans Peter, thank you for this fantastic post. Using the sqlSave method you can append the data to an existing table. Unfortunately you are creating duplicates if there is an overlap between existing and updating data. Is there a way to upsert/merge (update, insert, delete) a sql table?

    • 18. June 2018 at 20:26

      Hi Daniel
      Thank you for your comment.
      Power Query writes each record 3x, which unfortunately leads to undesired results.
      The only method I have found so far is writing to an SQL table. This table is then used as the output table for Select Distinct queries.

  • 8. April 2019 at 17:18

    first of all I would thank you for that fantastic post. But I have problem with exporting colums with date. It shows me “Microsoft.OleDb.Date. On PowerBI side it is set as “date” type, I try to change table column type in generated table from “varchar” which was generated by script to “datetime”, but it was not help.
    How should I solve this?
    Thank you in advance for answer.

    • 29. May 2019 at 8:18

      Hi Jiri
      This is an issue with how SQL handles dates.
      I solved it by converting the date to the SQL standard format for dates.
      For example, VALUES(DATE ‘2015-12-17’), or I converted the date to a running integer (20151217).

  • 10. January 2020 at 20:28

    Hi, I would ask you about replacing old values in table on SQL server – is is possible (something like “Replace Into”)? And thank you very much for very helpful work :-)..

    • 30. September 2020 at 10:18

      You could use a update statement, like that:
      UPDATE Customers
      SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
      WHERE CustomerID = 1;


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: