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.

Related Content you might be interested in

Current Title:

Small book image
Extending Power BI with Python and R
Luca Zavarella
Nov, 2021 | 558 pages
Small book image
Artificial Intelligence with Power BI
MaryJo Diepeveen
Apr, 2022 | 348 pages
Small book image
Power Query Cookbook
Andrea Janicijevic
Oct, 2021 | 412 pages
Small book image
Data Cleaning with Power BI
Gus Frazer
Feb, 2024 | 340 pages
Table of Contents (22 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Conventions used
Get in touch
Share Your Thoughts
1
Section 1: Best Practices for Using R and Python in Power BI
Section 1: Best Practices for Using R and Python in Power BI
Free Chapter
2
Chapter 1: Where and How to Use R and Python Scripts in Power BI
Chapter 1: Where and How to Use R and Python Scripts in Power BI
Technical requirements
Injecting R or Python scripts into Power BI
Using R and Python to interact with your data
R and Python limitations on Power BI products
Summary
3
Chapter 2: Configuring R with Power BI
Chapter 2: Configuring R with Power BI
Technical requirements
The available R engines
Choosing an R engine to install
Installing an IDE for R development
Configuring Power BI Desktop to work with R
Configuring the Power BI service to work with R
R visuals limitations
Summary
4
Chapter 3: Configuring Python with Power BI
Chapter 3: Configuring Python with Power BI
Technical requirements
The available Python engines
Choosing a Python engine to install
Installing an IDE for Python development
Configuring Power BI Desktop to work with Python
Configuring the Power BI service to work with R
Limitations of Python visuals
Summary
5
Section 2: Data Ingestion and Transformation with R and Python in Power BI
Section 2: Data Ingestion and Transformation with R and Python in Power BI
6
Chapter 4: Importing Unhandled Data Objects
Chapter 4: Importing Unhandled Data Objects
Technical requirements
Importing RDS files in R
Importing PKL files in Python
Summary
References
7
Chapter 5: Using Regular Expressions in Power BI
Chapter 5: Using Regular Expressions in Power BI
Technical requirements
A brief introduction to regexes
Validating data using regex in Power BI
Loading complex log files using regex in Power BI
Extracting values from text using regex in Power BI
Summary
References
8
Chapter 6: Anonymizing and Pseudonymizing Your Data in Power BI
Chapter 6: Anonymizing and Pseudonymizing Your Data in Power BI
Technical requirements
De-identifying data
Anonymizing data in Power BI
Pseudonymizing data in Power BI
Summary
References
9
Chapter 7: Logging Data from Power BI to External Sources
Chapter 7: Logging Data from Power BI to External Sources
Technical requirements
Logging to CSV files
Logging to Excel files
Logging to an Azure SQL server
Summary
References
10
Chapter 8: Loading Large Datasets beyond the Available RAM in Power BI
Chapter 8: Loading Large Datasets beyond the Available RAM in Power BI
Technical requirements
A typical analytic scenario using large datasets
Import large datasets with Python
Importing large datasets with R
Summary
References
11
Section 3: Data Enrichment with R and Python in Power BI
Section 3: Data Enrichment with R and Python in Power BI
12
Chapter 9: Calling External APIs to Enrich Your Data
Chapter 9: Calling External APIs to Enrich Your Data
Technical requirements
What a web service is
Registering for Bing Maps Web Services
Geocoding addresses using Python
Geocoding addresses using R
Accessing web services using Power BI
Summary
References
13
Chapter 10: Calculating Columns Using Complex Algorithms
Chapter 10: Calculating Columns Using Complex Algorithms
Technical requirements
The distance between two geographic locations
Implementing distances using Python
Implementing distances using R
The basics of linear programming
Definition of the LP problem to solve
Handling optimization problems with Python
Solving LP problems with R
Summary
References
14
Chapter 11: Adding Statistics Insights: Associations
Chapter 11: Adding Statistics Insights: Associations
Technical requirements
Exploring associations between variables
Correlation between numeric variables
Correlation between categorical and numeric variables
Summary
References
15
Chapter 12: Adding Statistics Insights: Outliers and Missing Values
Chapter 12: Adding Statistics Insights: Outliers and Missing Values
Technical requirements
What outliers are and how to deal with them
Identifying outliers
Implementing outlier detection algorithms
What missing values are and how to deal with them
Diagnosing missing values in R and Python
Implementing missing value imputation algorithms
Summary
References
16
Chapter 13: Using Machine Learning without Premium or Embedded Capacity
Chapter 13: Using Machine Learning without Premium or Embedded Capacity
Technical requirements
Interacting with ML in Power BI with data flows
Using AutoML solutions
Embedding training code in Power Query
Using trained models in Power Query
Using trained models in script visuals
Calling web services in Power Query
Summary
References
17
Section 3: Data Visualization with R in Power BI
Section 3: Data Visualization with R in Power BI
18
Chapter 14: Exploratory Data Analysis
Chapter 14: Exploratory Data Analysis
Technical requirements
What is the goal of EDA?
EDA with Python and R
EDA in Power BI
Summary
References
19
Chapter 15: Advanced Visualizations
Chapter 15: Advanced Visualizations
Technical requirements
Choosing a circular barplot
Implementing a circular barplot in R
Implementing a circular barplot in Power BI
Summary
References
20
Chapter 16: Interactive R Custom Visuals
Chapter 16: Interactive R Custom Visuals
Technical requirements
Why interactive R custom visuals?
Adding a dash of interactivity with Plotly
Exploiting the interactivity provided by HTML widgets
Packaging it all into a Power BI Custom Visual
Importing the custom visual package into Power BI
Summary
References
Why subscribe?
21
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts

R and Python limitations on Power BI products

The first question once you are clear on where to inject R and Python scripts in Power BI could be: "Is the use of R and Python code allowed in all Power BI products?" In order to have a brief recap of the various Power BI products and their usage in general, here is a concise list:

  • Power BI Service: This is sometimes called Power BI Online, and it's the Software as a Service (SaaS) declination of Power BI. It was created to facilitate the sharing of visual analysis between users through Dashboards and Reports.
  • Power BI Report Server: This is the on-premises version of Power BI and it extends the capabilities of SQL Server Reporting Services, enabling the sharing of reports created in Power BI Desktop (for Report Server).
  • Power BI Embedded: A Microsoft Azure service that allows dashboards and reports to be embedded in an application for users who do not have a Power BI account.
  • Power BI Desktop: A free desktop application for Windows that allows you to use almost all of the features that Power BI exposes. It is not the right tool for sharing results between users, but it allows you to share them on Power BI Service and Power BI Report Server. The desktop versions that allow publishing on the two mentioned services are distinct.
  • Power BI Mobile: A mobile application, available on Windows, Android, and iOS, that allows secure access to Power BI Service and Power BI Report Server, and that allows you to browse and share dashboards and reports, but not edit them.

Apart from the licenses, which we will not go into here, a summary figure of the relationships between the previously mentioned products follows:

Figure 1.15 – Interactions between Power BI products

Figure 1.15 – Interactions between Power BI products

Unfortunately, of all these products, only Power BI Service, Power BI Embedded, and Power BI Desktop allow you to enrich data via code in R and Python:

Figure 1.16 – Power BI products compatibility with R and Python

Figure 1.16 – Power BI products compatibility with R and Python

Important note

From here on out, when we talk about Power BI Service in terms of compatibility with analytical languages, what we say will also apply to Power BI Embedded.

So, if you need to develop reports using advanced analytics through R and Python, make sure the target platform supports them.

Previous Section
End of Section 5
Next Section