Hits: 8921

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

Prerequisites

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:

let
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:
library(RODBC)
conn <- odbcDriverConnect("driver=SQL Server; server=NBXYZ; Database=ExcelDemo")
odbcClearError(conn)
sqlSave(conn, dataset, tablename="Customers",rownames=FALSE, safer=FALSE, append=TRUE)
close(conn)

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!

Deine Bewertung
[Total: 7 Average: 4.3]

5 thoughts on “Export data to SQL

  • 18. June 2018 at 0:27
    Permalink

    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?

    Reply
    • 18. June 2018 at 20:26
      Permalink

      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.

      Reply

Leave a Reply

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

%d bloggers like this: