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
You can also set this from following the below steps: Go to Tools > Global Options > General
Once this is ready, Go to the R Console and write the below script to install RODBC Package.
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.
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
You can refer the Documentation for more on how the RODBC Package works.
- 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.)