Export Power BI tables to SQL Server database using R


In today’s post, we will learn two new techniques to load the Power BI Desktop data to the SQL Server or any other ODBC database.

Technique 1 : Using R Script in the Query Editor Window 

In this technique, we will leverage the power of R in the Query Editor. You can use R script in the Power Query Editor to do the custom data transformations using the various support R packages.

For this technique to work, you first need to download/install the latest version of the R from CRAN at here. Please select the appropriate version (32/64 bit).

It would also required to download/install the free & open source R IDE RStudio from here.

Once both installation is finished, You can open the R studio. There are few settings to be implemented before we can start harnessing the power of R.

Step 1 : Set the Working Directory in R

R wd

You can also set this from following the below steps: Go to Tools > Global Options > General

wd

Once this is ready, Go to the R Console and write the below script to install RODBC Package.

install.packages(“RODBC”)

This will install the RODBC package in your R Environment.

Step 2 : Set up the R IDE & R Directories in Power BI Desktop

Now, let’s go back to the Power BI Desktop and set the R environment.

Go to File > Options & Settings > Options > R Scripting and set up the correct R directory & R IDE.

R PBI

Step 3 : Import your data into Power BI &  Go to the Edit Queries mode

Step 4 : Select the table to be imported & right click and duplicate the table you would like to import.

Step 5: Go to the Transform Tab & select run R script 

R script 1

R Script

r script 2

You can refer the Documentation for more on how the RODBC Package works.

CONS:

  • Relatively slow
  • Data Conversion Errors due to mismatch of the data types between R , Power BI & SQL Server. This is particularly observed in the Date/Time Data type.
  • Memory intensive

In the next blog, I will show you how to use the SSIS to do the data transfer between Power BI Desktop & SQL Server/any other destinations ( Excel, CSV etc.)

 

 

 

 

 

 

 

 

 

Previous Power BI August 2017 Update
Next How do relationships work in Power BI?

No Comment

Leave a Reply