Skip to main content
main-content
Top

About this book

This easy-to-follow guide provides R and Python recipes to help you learn and apply the top languages in the field of data analytics to your work in Microsoft Power BI. Data analytics expert and author Ryan Wade shows you how to use R and Python to perform tasks that are extremely hard, if not impossible, to do using native Power BI tools. For example, you will learn to score Power BI data using custom data science models and powerful models from Microsoft Cognitive Services.

The R and Python languages are powerful complements to Power BI. They enable advanced data transformation techniques that are difficult to perform in Power BI in its default configuration but become easier by leveraging the capabilities of R and Python. If you are a business analyst, data analyst, or a data scientist who wants to push Power BI and transform it from being just a business intelligence tool into an advanced data analytics tool, then this is the book to help you do that.

What You Will Learn

Create advanced data visualizations via R using the ggplot2 package Ingest data using R and Python to overcome some limitations of Power Query Apply machine learning models to your data using R and Python without the need of Power BI premium compacity Incorporate advanced AI in Power BI without the need of Power BI premium compacity via Microsoft Cognitive Services, IBM Watson Natural Language Understanding, and pre-trained models in SQL Server Machine Learning Services Perform advanced string manipulations not otherwise possible in Power BI using R and Python

Who This Book Is For

Power users, data analysts, and data scientists who want to go beyond Power BI’s built-in functionality to create advanced visualizations, transform data in ways not otherwise supported, and automate data ingestion from sources such as SQL Server and Excel in a more concise way

Table of Contents

Frontmatter

Creating Custom Data Visualizations Using R

Frontmatter

Chapter 1. The Grammar of Graphics

Abstract
Data visualization has always been a very important part of statistics. It gives statisticians a way to share their findings with others in a concise and relatively easy-to-understand way. Given the fact that R is a programming language built by statisticians for statisticians, the R community has made a considerable effort to ensure that R users are able to effectively create visualizations to help them tell their data stories.
Ryan Wade

Chapter 2. Creating R Custom Visuals in Power BI Using ggplot2

Abstract
In the previous chapter, you were introduced to the ggplot2 package, and you were given a basic template to use for creating visuals in Power BI. In this chapter, you will be given some recipes that illustrate how expressive you can be when you leverage ggplot2 to create R custom visuals in Power BI. This chapter contains recipes to create the following charts:
Ryan Wade

Ingesting Data into the Power BI Data Model Using R and Python

Frontmatter

Chapter 3. Reading CSV Files

Abstract
Power BI is an excellent tool for data visualization and has been recognized as one of the premier tools in that category. However, before you are able to create the great visualizations that Power BI is known for, you first need to get data into the Power BI data model.
Ryan Wade

Chapter 4. Reading Excel Files

Abstract
Power BI has built-in tools that enable you to read Microsoft Excel files into the Power BI data model. The built-in functionality is intuitive and easy to use for simple workflows, but things can become unnecessarily difficult when the workflows get complicated. Many workflows that are difficult to do using native Power BI tools are relatively easy to do using R or Python. We will illustrate with an example.
Ryan Wade

Chapter 5. Reading SQL Server Data

Abstract
Power BI’s built-in ETL tool, Power Query, is capable of reading data that is stored in a variety of formats into memory, making transformations to the data while in memory, and loading the transformed data into the Power BI data model. It does a great job of doing these types of tasks as it can handle most ETL situations.
Ryan Wade

Chapter 6. Reading Data into the Power BI Data Model via an API

Abstract
Many governmental entities and private companies have made their data more accessible via Data APIs. Data APIs provide interfaces that enable you to programmatically retrieve data from data providers. The data sets that these Data APIs provide can bring great value to your Power BI data model.
Ryan Wade

Transforming Data Using R and Python

Frontmatter

Chapter 7. Advanced String Manipulation and Pattern Matching

Abstract
Many basic string manipulation and pattern matching tasks can be handled using one of the many string functions that come pre-package in Power Query. You can get to many of these functions from the graphical interface, and they are also available for use in calculated columns. These functions work great for simple situations but are not sufficient in many advanced scenarios. Fortunately, R and Python have advanced tools that enable you to perform advanced string manipulation and pattern matching tasks that are beyond the capabilities of Power Query.
Ryan Wade

Chapter 8. Calculated Columns Using R and Python

Abstract
A task that is difficult to do in Power Query is adding calculated columns that are based on complex mathematical formulas. That problem does not exist in R or Python. R was built by statisticians, so it was designed to perform complex statistical and mathematical calculations. The same holds true with Python. Python is not only used in data science but also in other computation-intensive fields such as engineering and physics. Like R, Python is optimized to be able to perform calculations that are not possible using M in Power Query. So, in situations where you need to add a calculated column based on a complicated computation in Power BI, you should consider leveraging R or Python. You will not only benefit from the fact that R and Python handle complex computations better but, in many cases, you may find that R and/or Python may have a pre-built function that does the heavy lifting for you.
Ryan Wade

Machine Learning and AI in Power BI Using R and Python

Frontmatter

Chapter 9. Applying Machine Learning and AI to Your Power BI Data Models

Abstract
Having a mature enterprise business intelligence solution has become the norm for many organizations. A considerable number of firms have well-governed Enterprise Data Warehouses (EDWs) that are updated via sophisticated ETL processes. They use reporting tools such as SQL Server Report Services (SSRS)  and Power BI to help the business gain valuable insights about their business. Organizations that fall in this category have done a great job of leveraging BI, and they are primed to introduce artificial intelligence (AI) to their data strategy. Here are a few ways AI can be used to enhance a mature business intelligence system:
Ryan Wade

Chapter 10. Productionizing Data Science Models and Data Wrangling Scripts

Abstract
The data wrangling scripts and data scoring scripts in the previous chapters work great in a self-service situation or in small shops where one person is responsible for maintaining the Power BI data models. That is because in those situations you can get away with using the personal version of the on-premises data gateway. But, the enterprise version of the on-premises data gateway is required for enterprise solutions, and it does not allow the use of R or Python scripts embedded in Power BI. Fortunately, you can overcome this limitation using a relatively new feature in SQL Server known as SQL Server Machine Learning Services (SSMLS). SSMLS is a feature of SQL Server that enables you to perform advanced data analytics inside the database via R and Python scripts that are wrapped in a special T-SQL stored procedure. Since you are able to fetch data via a stored procedure call using the on-premises data gateway, you can refactor your previously written data wrangling and data scoring scripts in Power BI to an enterprise solution by wrapping the scripts in a stored procedure.
Ryan Wade

Backmatter

Additional information

Premium Partner

    Image Credits