SQLServerCentral Article

How to Import Data in Power BI Using R

,

Introduction

In this article, we will learn how to import data in Power BI using R. R and Python are standard tools used in Power BI. In a previous article, we learned how to import data in Power BI using Python. This time we will learn to use R.

The article will cover the following topics:

  • How to import data in Power BI using R
  • Create scattered visuals from data imported in Power BI using R
  • Modify data in Power BI using R
  • Modify data in Power BI using R
  • How to import data in Power BI using R and the read.csv function

Requirements

First, we need to have the Power BI Desktop installed.

Secondly, we need internet access.

Thirdly, we need to have R installed.

Importing data in Power BI using R

First, in Power BI Desktop, select the Get data option.

Power BI get data

Secondly, in the search textbox, write the R script and press the Connect button.

R script

Thirdly, in the R script window, we will get data from a well-known dataset named iris. We will use the data function for that. In addition, write the following R script to get data from the iris dataset and press OK:

Data(iris)

import data in Power BI using R

Also, check the iris table and press the Load button.

import data in Power BI using R - Iris data set

The iris table contains the Length and width of the iris’ petals and sepals.

This data set is commonly used in data science to study multivariate data. The data collects the sepal length, Sepal Width, Petal width and Petal length. 50 samples of data are collected for each species of iris: the setosa, virginica and versicolor.

For more information about the iris dataset, refer to this link: Iris flower data set.

Create scattered visuals from data imported in Power BI using R

First, we imported data using R. We imported a dataset named iris. Now we are going to create some charts with the data. Check the Petal.Length and Petal.Width fields.

Secondly, select the scattered chart visual.

Thirdly in the X axis, drag and drop the Petal.Length and in the Y axis, drag and drop the Petal.Width.

import data in Power BI using R - Add to a scattered chart

The Fields by default show the totals. We do not want the sums, but just the values.

To do that, select the X Axis and click on the Petal.Lenght. Select the Don’t summarize option.

Also, repeat the same steps for the Petal.Width, click on the Petal.Width in the Y Axis and select the Don’t summarize option.

select the don't summarize option

Finally, if everything is OK, the visuals will show a chart like this one.

Show scattered chart

Modify data using R

Previously, we show how to import data using the R script option. Now, we will show how to modify the data using the R script. We will use the data already imported.

First, in fields, select the iris table, right-click and select Edit query.

import data in Power BI using R - Edit query

Secondly, in the new Windows select the Transformation option in the menu and select the Run R script icon.

import data in Power BI using R - Run R script

Thirdly, in this example, we will use the head function and select the first 5 rows of the dataset. The dataset is the iris data and press OK. The output will store the information used by the head function.

output <- head(dataset,5)

head function example

Also, if everything is OK, the function will show the first 5 rows.

sample of data with head

In addition, you can use the applied steps to redo and undo some steps.

Applied steps in Power BI

To edit the script, click on the configuration of the Run R Script step.

Edit R Script in Power BI

The following code will show the last 10 rows of the dataset using the tail function.

output <- tail(dataset,10)

Tail function example

Finally, if everything is OK, you will see 10 rows:

sample of tail data

How to import data in Power BI using R and the read.csv function

Another way to import data in Power BI using R is using the read.csv function. This function reads csv files. I will include iris.csv in the resources to download. In this example, I stored the iris.csv file in the c:\data folder. To import data from a CSV file, follow these steps:

First, in Power BI press the Get data icon.

Power BI Home Get data

Secondly, look for the R script and press connect.

R script

Write the following R code and press OK.

iris <- read.csv(file = 'c:\\data\\iris.csv')

Note that we need a double backslash (\\) for the paths.

read csv

Load the data and create your charts with the data imported.

Conclusion

In this article, we saw how to import data in Power BI using R. We can transform the data with R script of any dataset or import the data using R.

Rate

5 (1)

Share

Share

Rate

5 (1)