{"id":79267,"date":"2018-06-20T07:12:21","date_gmt":"2018-06-20T07:12:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=79267"},"modified":"2021-09-15T13:22:14","modified_gmt":"2021-09-15T13:22:14","slug":"power-bi-introduction-working-with-r-scripts-in-power-bi-desktop-part-3","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-introduction-working-with-r-scripts-in-power-bi-desktop-part-3\/","title":{"rendered":"Power BI Introduction: Working with R Scripts in Power BI Desktop \u2014 Part 3"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Power BI Introduction: Tour of Power BI \u2014 Part 1<\/a><\/li>\n
  2. Power BI Introduction: Working with Power BI Desktop \u2014 Part 2<\/a><\/li>\n
  3. Power BI Introduction: Working with R Scripts in Power BI Desktop \u2014 Part 3<\/a><\/li>\n
  4. Power BI Introduction: Working with Parameters in Power BI Desktop \u2014 Part 4<\/a><\/li>\n
  5. Power BI Introduction:\u00a0Working with SQL Server data in Power BI Desktop \u2014 Part 5<\/a><\/li>\n
  6. Power BI Introduction:\u00a0Power Query M Formula Language in Power BI Desktop \u2014 Part 6<\/a><\/li>\n
  7. Power BI Introduction: Building Reports in Power BI Desktop \u2014 Part 7<\/a><\/li>\n
  8. Power BI Introduction: Publishing Reports to the Power BI Service \u2014 Part 8<\/a><\/li>\n
  9. Power BI Introduction: Visualizing SQL Server Audit Data \u2014 Part 9<\/a><\/li>\n<\/ol>\n\n

    Power BI Desktop provides a powerful tool for creating reports you can publish to the Power BI service or save to Power BI Report Server. One of the most valuable features in Power BI Desktop is its integration with the R language. You can use R to import or transform data, as well as create visualizations. You can also import prebuilt R-based visualizations into Power BI Desktop, without needing to write or interact with R.<\/p>\n

    In this article, I explain how to work with R in Power BI Desktop, focusing on the how to perform R-specific tasks, as opposed to diving into the R language. For this reason, the R scripts in the examples are very rudimentary and meant only to demonstrate how you can utilize R in Power BI Desktop. Once you understand these concepts, you can then take advantage of R\u2019s extensive and robust capabilities to analyze, transform, and visualization data. If you\u2019re not familiar with R, a good place to start is with the R Project site<\/a>.<\/p>\n

    When working with R in Power BI Desktop, R must be installed on the same Windows computer as Power BI Desktop. You can download and install an R distribution for free from a variety of locations, including the CRAN distribution available from the R Project site<\/a> and the Microsoft R Open distribution available from the Revolution Analytics site<\/a>. For the examples in this article, I used the CRAN distribution, version 3.5.0.<\/p>\n

    You\u2019ll also find it handy to have a separate R integrated development environment (IDE) installed on your system. This allows you to verify your R scripts before running them within Power BI Desktop. An IDE also makes it easier to install R packages that you might need when running R in Power BI Desktop. On my system, I installed the free version of RStudio, which is available from the RStudio site<\/a>.<\/p>\n

    When you open Power BI Desktop, Power BI Desktop tries to locate an R installation on your system. You can verify whether the correct installation has been detected by opening the Options<\/em> dialog box, available by going to File > Options and settings > Options<\/em>. The following figure shows the Options<\/em> dialog box with the R scripting<\/em> section selected, as it appears on my system.<\/p>\n

    <\/p>\n

    In my case, Power BI Desktop detected both the correct R installation and IDE. If you find the information to be incorrect or missing, you can provide a specific R installation, R IDE, or both.<\/p>\n

    Associating an IDE with Power BI Desktop applies only when creating R visualizations in Report<\/em> view. By associating an IDE, you can launch the IDE from within Power BI Desktop and work on the visualization script there.<\/p>\n

    Using an R Script to Import Data<\/h2>\n

    When using an R script to import data, the script must return at least one data frame that serves as the basis for your imported table. If the script returns multiple data frames, you can choose which ones to include as part of the import process. Power BI will create a table for each imported data frame. One word of warning, however. If a data frame contains a column configured with the complex<\/strong> or vector<\/strong> type, Power BI Desktop will replace the column\u2019s values with errors.<\/p>\n

    To use an R script to import data into Power BI Desktop, click the Get Data<\/em> button on the Home<\/em> ribbon. When the Get Data<\/em> dialog box appears, navigate to the R script <\/em>option in the Other<\/em> category, as shown in the following figure.<\/p>\n

    <\/p>\n

    When you click the Connect<\/em> button, Power BI Desktop launches the R script<\/em> dialog box, where you can type or paste your R script. For the first example, use the following R script to retrieve data from the iris<\/em> dataset (included with the CRAN distribution) and assign it to the iris_raw<\/em> variable:<\/p>\n

    iris_raw <- iris<\/pre>\n
    In Power BI Desktop, you must assign the dataset to a variable even if you don\u2019t modify the data frame in any way. If you were to enter only the name of the dataset, as you can do in an IDE, no data frames would be available to import into Power BI Desktop.<\/p>\n
    Before entering your script into the R script<\/em> dialog box, you should test it in your IDE to ensure that it runs properly and returns the results you expect. If your script generates an error within Power BI Desktop, it can be very difficult to make sense of it. Plus, you have to start over with the import process.<\/p>\n
    Once you\u2019re sure that the script is ready to go, you can enter it into the Script<\/em> textbox, as shown in the following figure.<\/p>\n
    <\/p>\n
    When you click OK,<\/em> Power BI Desktop processes the script and then launches the Navigator<\/em> dialog box, which lets you select which data frames to import we well as review sample data for each data frame. In this case, the R script returns only the iris_raw<\/strong> data frame, so it is the only one available, as shown in the following figure.<\/p>\n
    <\/p>\n
    The Navigator<\/em> dialog box lists the data frames in the Display Options<\/em> section, under the R[1]<\/em> folder. You must select the checkbox associated with each data frame you want to import. Once you\u2019ve made your selection, click Load<\/em>. After Power BI Desktop loads the data, you can view the dataset in Data<\/em> view, as shown in the following figure.<\/p>\n
    <\/p>\n
    In some cases, you might want to import data from a file, rather than using one of the built-in datasets. For example, suppose you\u2019ve copied the iris<\/em> dataset to a CSV file, which you\u2019ve saved to the C:\\DataFiles\\<\/em> folder on your local drive. You can easily pull the data into an R data frame using the following statement:<\/p>\n
    iris_csv <- read.csv(file=\"C:\/DataFiles\/iris.csv\", header=TRUE, sep=\",\")<\/pre>\n
    The statement uses the read.csv<\/strong> function to read the context of the iris.csv<\/em> file. The header<\/strong> argument is set to TRUE<\/strong> to indicate that the first-row values should be created as headers (if that\u2019s what you want to do.) The sep<\/strong> argument indicates that a comma is used to separate the data values within the file. You can then use the same process described in the previous example to import the data from the iris_csv<\/strong> data frame.<\/p>\n
    The R language is very flexible due to the ability to install packages with additional functionality, and there are several that aid reporting and analytics. The dplyr<\/strong> and data.table<\/strong> packages provide useful functions (verbs) for working with data frames. The ggplot2<\/strong> is useful for visualizations. To use these in Power BI Desktop, they must be installed on your system. You can install these through your IDE, in this case R Studio. Launch R Studio and run these commands:<\/p>\n
    install.packages(\"dplyr\")\r\ninstall.packages(\"data.table\")\r\ninstall.packages(\"ggplot2\")<\/pre>\n
    One of the biggest benefits of being able to use R when importing data into Power BI Desktop is that you can manipulate data as part of the import process. For example, the following script uses the summarize<\/strong> and group_by<\/strong> functions available in the dplyr<\/strong> R package to group and aggregate the data before importing it:<\/p>\n
    library(dplyr)\r\niris_mean <- summarize(group_by(iris, Species),\r\n  slength = mean(Sepal.Length), swidth = mean(Sepal.Width),\r\n  plength = mean(Petal.Length), pwidth = mean(Petal.Width))<\/pre>\n
    The group_by<\/strong> function prepares the data for use by another function, which in this case is summarize<\/strong> (also written as summarise<\/strong>). In this example, the summarize<\/strong> function is used in conjunction with the mean<\/strong> function to find the mean for each of the four measures, grouped according to the values in the Species<\/em> column.<\/p>\n
    Returning now to the R statement above, notice that the aggregated data is saved to the iris_mean <\/strong>variable. This is the name that is assigned to the dataset when imported into Power BI Desktop, as shown in the following figure.<\/p>\n
    <\/p>\n
    Of course, you can write far more complex R scripts than the one shown here, but this should give you an idea of how easy it is to use R to import data into Power BI Desktop. And the more adept you are with R, the more powerful this feature becomes.<\/p>\n
    Using an R Script to Transform Data<\/h2>\n
    There might be times when you\u2019ll want to use R to manipulate a dataset already imported into Power BI Desktop. Query Editor includes the capacity to apply an R script to a dataset in order to transform the data. But before modifying the data, take a look at the following figure, which shows how the iris_raw<\/em> dataset appears in Query Editor before applying any transformations.<\/p>\n
    <\/p>\n
    Notice that the Applied Steps<\/em> section includes two steps: Source<\/em> and Navigation<\/em>. In this figure, the Source<\/em> step is selected and a small table is displayed in Query Editor\u2019s main pane. The table represents the initial import operation, with one row included for each returned data frame. In this case, because there is only the iris_raw<\/em> data frame, the table includes only one row. The Table<\/em> value in the Value<\/em> column represents the data associated with that data frame. Selecting this value results in the second step, Navigation,<\/em> which is the actual imported data, as shown in the following figure. Whenever you use an R script to import data, Power BI Desktop adds both the Source<\/em> and Navigation<\/em> steps.<\/p>\n
    <\/p>\n
    Now take a look at how to run an R script against the iris_raw<\/em> dataset. To keep things simple, use the same aggregation logic used when importing the iris_mean<\/em> dataset. The primary difference is that you must use the dataset<\/strong> variable when referencing the dataset, instead of specifying iris<\/strong>, as shown in the following script:<\/p>\n
    library(dplyr)\r\niris_mean <- summarize(group_by(dataset, Species),\r\n  slength = mean(Sepal.Length), swidth = mean(Sepal.Width),\r\n  plength = mean(Petal.Length), pwidth = mean(Petal.Width))<\/pre>\n
    To run this or any R script in Query Editor, click the Run R Script<\/em> button on the Transform<\/em> ribbon. This launches the Run R Script<\/em> dialog box, which includes the Script<\/em> textbox, where you can type or paste your script. The following figure shows the Run R Script<\/em> dialog box with the above R script included. Notice that Power BI Desktop adds a comment stating that the dataset<\/strong> variable holds the input data. The input data is the active dataset in Query Editor, which in this case is the iris_raw<\/em> dataset.<\/p>\n
    <\/p>\n
    After you enter the script and click OK,<\/em> Query Editor adds two steps to the Applied Steps<\/em> section: Run R Script<\/em> and “iris_mean”<\/em>. These two steps work just like the first two steps, Source<\/em> and Navigation<\/em>. The first one reflects the data frames returned by the script, and the second reflects the selected data frame, as shown in the following figure.<\/p>\n
    <\/p>\n
    As with using R to import data, being able to run an R script against a dataset provides you with a powerful tool for working with the imported data, whether the data was imported from a database system, online service, or text file. After you\u2019ve imported the data into Power BI Desktop, any dataset is fair game.<\/p>\n
    Using an R Script to Create Visualizations<\/h2>\n
    Another great feature in Power BI Desktop is the ability to use R to create visualizations within Report<\/em> view. For the most part, this process is as straightforward as using R in other ways, except for one important caveat: R automatically groups and summarizes the data, whether or not you want done, and you cannot override this behavior. Why Microsoft decided to take this approach is a bit of a mystery, especially since it can sometimes give you results you don\u2019t want.<\/p>\n
    Fortunately, there is a workaround. You can add a column to your dataset that uniquely identifies each row, similar to an IDENTITY<\/strong> column in a SQL Server table. If you\u2019re using an R script to import the data, you can add the column as part of the import process. For example, the following R script adds an identifier column to the iris<\/strong> dataset based on the dataset\u2019s index (row names):<\/p>\n
    library(data.table)\r\niris_id <- iris\r\niris_id <- setDT(iris_id, keep.rownames=TRUE)[]\r\nsetnames(iris_id, 1, \"id\")\r\niris_id$id <- as.integer(iris_id$id)<\/pre>\n
    The script starts by calling the data.table<\/strong> package, which provides functions for working with data frame objects. (Be sure to install the package if you haven\u2019t already done so.) The setDT<\/strong> function, along with the keep.rownames<\/strong> argument, creates the new column based on the index values. Notice that you must first assign the iris<\/strong> dataset to the iris_id<\/strong> variable before using the setDT<\/strong> function. This is because the function makes changes directly to the dataset, which you cannot do with a built-in dataset such as iris<\/strong>.<\/p>\n
    After creating the column, you can then use the setnames<\/strong> function to change the name of the first column from the default (rn<\/strong>) to the new name (id<\/strong>). The final step is to change the column data type to integer<\/strong>.<\/p>\n
    With the iris_id<\/em> dataset in place, you can use it to create a visualization based on an R script. To add an R-based visualization, go to Report<\/em> view and click the R<\/em> button on the Visualizations<\/em> pane. The first time you do this, you\u2019ll be prompted to enable script visualizations, as shown in the following figure. Simply click the Enable<\/em> button, and you\u2019ll be good to go.<\/p>\n
    <\/p>\n
    When you click the R<\/em> button on the Visualizations<\/em> pane, Power BI Desktop adds a graphic placeholder to the report and opens the R script editor<\/em> pane. Before you can do any scripting, however, you need to identify the dataset columns that you\u2019ll be using in the visualization. The easiest way to do this is to drag the columns from the Fields<\/em> pane to the Values<\/em> section of the Visualizations<\/em> pane. Be sure to include the identifier column you created on the dataset. For this example, add the id, Species, Petal.Length,<\/em> and Petal.Width<\/em> columns, as shown in the following figure.<\/p>\n
    <\/p>\n
    When you add the columns, Power BI Desktop inserts several comments in the R script editor<\/em> pane. The first two comments indicate that a data frame named dataset<\/strong> has been created, based on the columns you added to the Values<\/em> section of the Visualizations<\/em> pane. You must use dataset<\/strong> to reference the source data in your R script.<\/p>\n
    The second two comments, along with the warning message at the top of the pane, indicate that duplicate rows have been removed from the dataset, which is why you needed to add the identifier column.<\/p>\n
    Beneath the comments, you can type or paste your R script. For this example, use the following script to create a basic scatter plot:<\/p>\n
    library(ggplot2)\r\nggplot(data=dataset, aes(x=Petal.Width, y=Petal.Length)) +\r\n  geom_point(aes(color=Species), size=3) +\r\n  ggtitle(\"Petal Widths and Lengths\") +\r\n  labs(x=\"Petal Width\", y=\"Petal Length\") +\r\n  theme_bw() +\r\n  theme(title=element_text(size=15, color=\"blue3\"))<\/pre>\n
    The script uses the ggplot<\/strong> function in the ggplot2<\/strong> package (be sure to install the ggplot2<\/strong> package if you haven\u2019t already) to create a visualization with the specified colors and labels. Notice that the Petal.Width<\/strong> column is used for the X-axis, the Petal.Length<\/strong> column is used for the Y-axis, and the Species<\/strong> column serves as the basis for the plot colors.<\/p>\n
    After you define the R script, click the Run script<\/em> button at the top of the R script editor<\/em> pane. Power BI Desktop processes the script and displays the visualization in the placeholder added earlier, when you first clicked the R<\/em> button. The following figure shows how the visualization appears on my system.<\/p>\n
    <\/p>\n
    You can update an R-based visualization at any time by modifying the R script. After you modify the script, click the Run script<\/em> button to update the visualization.<\/p>\n
    If you want to edit the code in your IDE, click Edit script in external R IDE<\/em> at the top of the R script editor<\/em> pane. This launches the IDE, which will display an R script that includes the code necessary to connect to the data source you\u2019re using for your visualization. The script will also include any code you already added to the visualization script in Power BI Desktop. You still have to copy-and-paste the bulk of your script back into Power BI Desktop, if you make any changes, but this at least saves you the trouble of trying to set up the data source in the IDE in order to test your script.<\/p>\n
    Something to keep in mind when adding R-based visualizations to your report is that the Power BI service comes with licensing restrictions. You cannot use R-based visualizations in the Power BI service unless you have a Power BI Pro license.<\/p>\n
    Importing R-based Custom Visuals<\/h2>\n
    Another fun feature in Power BI Desktop is the ability to import predefined R-based visualizations into your workspace. The visualizations are available through Microsoft\u2019s AppSource gallery, which you can access directly through Power BI Desktop. You do not need to understand R syntax, nor do you need build or run R scripts.<\/p>\n
    To import a visualization, click the ellipses button on the Visualizations<\/em> pane in Report<\/em> view, and then click Import from marketplace.<\/em> This launches the Power BI Visuals<\/em> window, shown in the following figure.<\/p>\n
    <\/p>\n
    When you locate the visual you want to import, click Add<\/em>. If the visualization requires additional R packages to be installed, the R Packages Required<\/em> dialog box appears, listing the packages that need to be added. You can either click Cancel<\/em> and install the packages manually, or you can click the Install<\/em> button and let Power BI Desktop install the packages automatically.<\/p>\n
    For this example, select the Spline chart<\/em> visualization. Type Spline in the Search<\/em> window if it\u2019s hard to find. When it\u2019s added it to Power BI Desktop, the R Packages Required<\/em> dialog box in the following figure will appear. To take the automatic installation route, click Install<\/em>.<\/p>\n
    <\/p>\n
    When you import a custom visualization, Power BI Desktop adds a button to the Visualizations<\/em> pane specific to that visualization. You can then add the visualization to your report and configure just like you would any of the prebuilt visualizations. The following figure shows the Spline chart<\/em> visualization. For the data, specify the Species, Sepal.Length,<\/em> and Sepal.Width<\/em> columns from the iris_id<\/em> dataset, as shown in the following figure.<\/p>\n
    <\/p>\n
    Microsoft provides a number of free and interesting custom visualizations in the AppSource gallery. It\u2019s certainly worth familiarizing yourself with what\u2019s out there so you can create reports that are even more engaging than you can achieve with the built-in visualizations. Best of all, Microsoft has made it exceptionally easy to pull the custom visualizations into Power BI Desktop, so you have little to lose by trying them out.<\/p>\n
    Making the Most of R in Power BI<\/h2>\n
    The integration of the R language into Power BI Desktop provides a powerful tool for transforming and presenting business intelligence data. R is a comprehensive statistical computing and graphics language that is extensively implemented and has a large and active user community. In Power BI Desktop, you can use R to import and modify data as well as create a wide range of visualizations that provide in-depth insights into the data. Those already well versed in R should find that using R in Power BI Desktop is a simple and straightforward process. Even those less experienced with R could find it well worth the effort to try out the Power BI Desktop R features.<\/p>\n","protected":false},"excerpt":{"rendered":"
    R is a powerful language for manipulating data and creating visualizations. In this article in the series, Robert Sheldon demonstrates how R can be used within Power BI Desktop to import data and to create visualizations.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[],"coauthors":[6779],"class_list":["post-79267","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79267","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=79267"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79267\/revisions"}],"predecessor-version":[{"id":80514,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/79267\/revisions\/80514"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=79267"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=79267"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=79267"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=79267"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
    After the packages are installed, you can then use the library<\/strong> function within your R script to call that package when importing the data. This allows you to use the functions included in the package, such as group_by<\/strong> and summarize<\/strong>. To view a list of the packages that Power BI Desktop supports, see the help topic R packages in the Power BI service<\/a>.<\/p>\n