Export Data from Power BI into a file using R

Export Data from Power BI into a file using R

We usually import Data from file into Power BI, but exporting data from Power BI can be very handy when you want to create a custom visual using R.

In fact it can be very cumbersome to code your visual directly into the Power BI script editor.
Here are few reasons why you should opt for exporting your Power Bi dataset first and re-import it in R to create your visual.

  • Intellisense is not available in Power BI R script embedded
  • Does not highlight keywords in colour
  • Hard to debug & hard to code (you can’t print intermediate calculation)
  • Slower than Rstudio

So unlike you’re a R master or you want to create a very simple visual it is definitely worth exporting your data into a file and then re-import it into R.

You can then create your visual in Rstudio first and once you’re happy with it just copy and paste your code into the Power BI visual script.

Export you data

If you haven’t already installed the package (gdata) you’ll need to install it:

#open an instance of R and type the command below
install.packages("gdata");

 

Once the “gdata” package is installed, select the R visual script and drag into values the measures and columns you need.

Capture

In the R script editor type the following R code:

require(gdata)
write.table(trim(dataset), file="your filepath.txt", sep = "\t", row.names = FALSE)

Capture

You can add plot(dataset) like I did int the above screenshot to make sure there isn’t any errors in your script hence as long as you can see a plot whatever it is(line-plot, box-plot, correlation-plot) it means your export was successful or obviously you can just check if your file is present in your directory.

Here is my output file:
Capture

Re-import you Power BI dataset into R

Now we can import our Power BI dataset into R as follows:

dataset = read.table(file="myfile2.txt", sep = "\t",header = TRUE)

See the R output below:
Capture

You can now work with your dataset in Rstudio until you get your visual right and then you’ll just need to copy & paste your code into the Power Bi script..

 

Leave a Reply

Your email address will not be published. Required fields are marked *