.png?width=165&name=Untitled%20design(66).png "Untitled design(66)")
There are two different ways to apply R in Power BI: the R Script for loading and transforming data, and the R Visual for additional enhancement and data visualization. The following tutorial video walks through a basic scenario using both. You can also follow the step-by-step tutorial below.
Thanks for reporting a problem. We'll attach technical data about this session to help us figure out the issue. Which of these best describes the problem?
Any other details or context?
The first time you use Power BI, you will need to locate your local R installation. This is a one-time setup unless you upgrade R in the future.
Open Power BI Desktop. Close the startup window with
Go to File, select Options
Under the Global heading, select R Scripting. Power BI attempts to automatically detect a local R installation. If you have multiple versions of R installed, you can select an alternate version from the menu.
Click OK when done.
Copy and paste the following code into the Execute R Script window, then click OK. This code simply references a remote CSV file and loads it into a data frame without transforming it in any way. In the real world, to take true advantage of the R Script, you can do some more advanced data cleansing and transformation with R prior to executing the script.
The sample CSV contains three columns: Console, Date, and Search. The Console column contains a record of a particular gaming such as Xbox or PS4. The Date column contains the record's date of search. The Search column contains the number of searches for the console for the given date.
source <- "https://cdn.rawgit.com/BlueGranite/Microsoft-R-Resources/master/power-bi/gameconsole.csv"
game.console <- read.csv(source, header = TRUE)
On the Navigator window, check the box next to game.console and then click Load. You should see “game console” appears in the “Fields” panel with three fields: Console, Date, and Search.
Now, click on the R visual in the Visualizations panel. You may also receive a message stating that you need to Enable Script Visuals. If so, select Enable. An R script editor appears on the report canvas. A message states that you need to drag fields into the Values area.
Click on Console, Date, and Search from the Fields panel and drag them into Values.
Note that the R script editor changes to show these fields and adds them to a data frame.
Copy and paste the following code into the R script editor, then click Run.
Note that you may need to first install the forecast package in your local R environment using install.packages(“forecast”).
In the code below, the aggregate function sums each search by date. The ts function converts a numeric R vector to an R time series object. The stl function uses Loess to remove any seasonal trend or other irregularities. The forecast function forecasts the result for the number of periods specified by the h argument. The plot function provides the final visualization for Power BI.
library(forecast)
# group searches by date
dataset <- aggregate(Search ~ Date, data = dataset, FUN= sum)
# create a time series based on month
ts <- ts(dataset$Search, frequency=4)
# pull out the seasonal, trend, and irregular components from the time series (train the forecast model)
decom <- stl(ts, s.window = "periodic")
# predict the next 3 months of searches
pred <- forecast(decom, h = 12)
# plot the predicted searches
plot(pred)
After a few seconds, you should see a plot display on the report.
Add a second R visual to the report canvas and drag the Console, Date, and Search from the Fields panel into Values.
Copy and paste the following code into the new R script editor, then click Run. This script also requires the forecast package. After a few seconds, you should see a plot display on the report.
library(forecast) # group searches by date dataset <- aggregate(Search ~ Date, data = dataset, FUN= sum) # create a time series based on month ts <- ts(dataset$Search, frequency=4) # pull out the seasonal, trend, and irregular components from the time series (train the forecast model) decom <- stl(ts, s.window = "periodic") plot(decom)
Add a Slicer visualization to the report canvas and add Console to the slicer’s Values. Check the box next to each of the three gaming consoles to see how the forecast changes for each.
