Book Image

Extending Power BI with Python and R

By : Luca Zavarella
Book Image

Extending Power BI with Python and R

By: Luca Zavarella

Overview of this book

Python and R allow you to extend Power BI capabilities to simplify ingestion and transformation activities, enhance dashboards, and highlight insights. With this book, you'll be able to make your artifacts far more interesting and rich in insights using analytical languages. You'll start by learning how to configure your Power BI environment to use your Python and R scripts. The book then explores data ingestion and data transformation extensions, and advances to focus on data augmentation and data visualization. You'll understand how to import data from external sources and transform them using complex algorithms. The book helps you implement personal data de-identification methods such as pseudonymization, anonymization, and masking in Power BI. You'll be able to call external APIs to enrich your data much more quickly using Python programming and R programming. Later, you'll learn advanced Python and R techniques to perform in-depth analysis and extract valuable information using statistics and machine learning. You'll also understand the main statistical features of datasets by plotting multiple visual graphs in the process of creating a machine learning model. By the end of this book, you’ll be able to enrich your Power BI data models and visualizations using complex algorithms in Python and R.
Table of Contents (22 chapters)
1
Section 1: Best Practices for Using R and Python in Power BI
5
Section 2: Data Ingestion and Transformation with R and Python in Power BI
11
Section 3: Data Enrichment with R and Python in Power BI
17
Section 3: Data Visualization with R in Power BI

Injecting R or Python scripts into Power BI

In this first section, Power BI Desktop tools that allow you to use Python or R scripts will be presented and described in detail. Specifically, you will see how to add your own code during the data loading, data transforming, and data viewing phases.

Data loading

One of the first steps required to work with data in Power BI Desktop is to import it from external sources:

  1. There are many connectors that allow you to do this, depending on the respective data sources, but you can also do it via scripts in Python and R. In fact, if you click on the Get data icon in the ribbon, not only the most commonly used connectors are shown, but you can select other ones from a more complete list by clicking on More...:
    Figure 1.1 – Browse more connectors to load your data

    Figure 1.1 – Browse more connectors to load your data

  2. In the new Get Data window that pops up, simply start typing the string script into the search text box, and immediately the two options for importing data via Python or R appear:
    Figure 1.2 – Showing R script and Python script into the Get Data window

    Figure 1.2 – Showing R script and Python script into the Get Data window

  3. Reading the contents of the tooltip, obtained by hovering the mouse over the Python script option, two things should immediately jump out at you:

    a) A local installation of Python is required.

    b) What can be imported through Python is a data frame.

    The same two observations also apply when selecting R script. The only difference is that it is possible to import a pandas DataFrame when using Python (a DataFrame is a data structure provided by the pandas package), whereas R employs the two-dimensional array-like data structure called an R data frame, which is provided by default by the language.

  4. After clicking on the Python script option, a new window will be shown containing a text box for writing the Python code:
    Figure 1.3 – Window showing the Python script editor

    Figure 1.3 – Window showing the Python script editor

    As you can see, it's definitely a very skimpy editor, but in Chapter 3, Configuring Python with Power BI, you'll see how you can use your favorite IDE to develop your own scripts.

  5. Taking a look at the warning message, Power BI reminds you that no Python engine has been detected, so it must be installed. Clicking on the How to install Python link will cause a Microsoft Docs web page to open, explaining the steps to install Python.

    Microsoft suggests installing the base Python distribution, but in order to follow some best practices on environments, we will install the Miniconda distribution. The details of how to do this and why will be covered in Chapter 3.

  6. If you had clicked on R script instead, a window for entering code in R, similar to the one shown in Figure 1.4, would have appeared:
Figure 1.4 – Window showing the R script editor

Figure 1.4 – Window showing the R script editor

As with Python, in order to run code in R, you need to install the R engine on your machine. Clicking on the How to install R link will open a Docs page where Microsoft suggests installing either Microsoft R Open or the classic CRAN R. Chapter 2, Configuring R With Power BI, will show you which engine to choose and how to configure your favorite IDE to write code in R.

In order to import data using Python or R, you need to write code in the editors shown in Figure 1.3 and Figure 1.4 that assigns a pandas DataFrame or an R dataframe to a variable, respectively. You will see concrete examples throughout this book.

Next, let's look at transforming data.

Data transformation

It is possible to apply a transformation to data already imported or being imported, using scripts in R or Python. Should you want to test this on the fly, you can import the following CSV file directly from the web: http://bit.ly/iriscsv. Follow these steps:

  1. Simply click on Get data and then Web to import data directly from a web page:
    Figure 1.5 – Select the Web connector to import data from a web page

    Figure 1.5 – Select the Web connector to import data from a web page

  2. You can now enter the previously mentioned URL in the window that pops up:
    Figure 1.6 – Import the Iris data from the web

    Figure 1.6 – Import the Iris data from the web

    Right after clicking OK, a window will pop up with a preview of the data to be imported.

  3. In this case, instead of importing the data as-is, click on Transform Data in order to access the Power Query data transformation window:
    Figure 1.7 – Imported data preview

    Figure 1.7 – Imported data preview

  4. It is at this point that you can add a transformation step using a Python or R script by selecting the Transform tab in Power Query Editor:
    Figure 1.8 – R and Python script tools into Power Query Editor

    Figure 1.8 – R and Python script tools into Power Query Editor

  5. By clicking on Run Python script, you'll cause a window similar to the one you've already seen in the data import phase to pop up:
    Figure 1.9 – The Run Python script editor

    Figure 1.9 – The Run Python script editor

    If you carefully read the comment in the text box, you will see that the dataset variable is already initialized and contains the data present at that moment in Power Query Editor, including any transformations already applied. At this point, you can insert your Python code in the text box to transform the data into the desired form.

  6. A similar window will open if you click on Run R script:
Figure 1.10 – The Run R script editor

Figure 1.10 – The Run R script editor

Also, in this case, the dataset variable is already initialized and contains the data present at that moment in Power Query Editor. You can then add your own R code and reference the dataset variable to transform your data in the most appropriate way.

Next, let's look at visualizing data.

Data visualization

Finally, your own Python or R scripts can be added to Power BI to create new visualizations, in addition to those already present in the tool out of the box:

  1. Assuming we resume the data import activity begun in the previous section, once the Iris dataset is loaded, simply click Cancel in the Run R script window, and then click Close & Apply in the Home tab of Power Query Editor:
    Figure 1.11 – Click Close & Apply to import the Iris data

    Figure 1.11 – Click Close & Apply to import the Iris data

  2. After the data import is complete, you can select either the R script visual or Python script visual option in the Visualizations pane of Power BI:
    Figure 1.12 – The R and Python script visuals

    Figure 1.12 – The R and Python script visuals

  3. If you click on Python script visual, a window pops up asking for permission to enable script code execution, as there may be security or privacy risks:
    Figure 1.13 – Enable the script code execution

    Figure 1.13 – Enable the script code execution

  4. After enabling code execution, in Power BI Desktop you can see a placeholder for the Python visual image on the report canvas and a Python script editor at the bottom:
Figure 1.14 – The Python visual layout

Figure 1.14 – The Python visual layout

You can now write your own custom code in the Python editor and run it via the Run script icon highlighted in Figure 1.14 to generate a Python visualization.

A pretty much identical layout occurs when you select R script visual.