Hits: 23724
Update 14. May 2019:
There is another approach using Power Query in SSIS:
https://www.powerbi-pro.com/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…..
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!
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.
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!
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?
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.
Hi,
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.
Jiri
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).
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 :-)..
Hi
You could use a update statement, like that:
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;