Extending Power BI with Python and R: Ingest, transform, enrich, and visualize data using the power of analytical languages
- Length: 558 pages
- Edition: 1
- Language: English
- Publisher: Packt Publishing
- Publication Date: 2021-11-26
- ISBN-10: 1801078203
- ISBN-13: 9781801078207
- Sales Rank: #66386 (See Top 100 Books)
Perform more advanced analysis and manipulation of your data beyond what Power BI can do to unlock valuable insights using Python and R
Key Features
- Get the most out of Python and R with Power BI by implementing non-trivial code
- Leverage the toolset of Python and R chunks to inject scripts into your Power BI dashboards
- Implement new techniques for ingesting, enriching, and visualizing data with Python and R in Power BI
Book Description
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.
What you will learn
- Discover best practices for using Python and R in Power BI products
- Use Python and R to perform complex data manipulations in Power BI
- Apply data anonymization and data pseudonymization in Power BI
- Log data and load large datasets in Power BI using Python and R
- Enrich your Power BI dashboards using external APIs and machine learning models
- Extract insights from your data using linear optimization and other algorithms
- Handle outliers and missing values for multivariate and time-series data
- Create any visualization, as complex as you want, using R scripts
Who this book is for
This book is for business analysts, business intelligence professionals, and data scientists who already use Microsoft Power BI and want to add more value to their analysis using Python and R. Working knowledge of Power BI is required to make the most of this book. Basic knowledge of Python and R will also be helpful.
Table of Contents
- Where and How to Use R and Python Scripts in Power BI
- Configuring R with Power BI
- Configuring Python with Power BI
- Importing Unhandled Data Objects
- Using Regular Expressions in Power BI
- Anonymizing and Pseudonymizing Your Data in Power BI
- Logging Data From Power BI To External Sources
- Loading Large Datasets Beyond the Available RAM in Power BI
- Calling External APIs to Enrich Your Data
- Calculating Columns Using Complex Algorithms
- Adding Statistics Insights: Associations
- Adding Statistics Insights: Outliers and Missing Values
- Using Machine Learning Without Premium or Embedded Capacity
- Exploratory Data Analysis
- Advanced Visualizations
- Interactive R Custom Visuals
B17081_16_Idx_SD Extending Power BI with Python and R: Ingest, transform, enrich and visualize using the power of analytical languages 1 Where and How to Use R and Python Scripts in Power BI Technical requirements Injecting R or Python scripts into Power BI Data loading Data transformation Data visualization Using R and Python to interact with your data R and Python limitations on Power BI products Summary 2 Configuring R with Power BI Technical requirements The available R engines The CRAN R distribution The Microsoft R Open distribution and MRAN Microsoft R Client Choosing an R engine to install The R engines used by Power BI Installing the suggested R engines Installing an IDE for R development Installing RStudio Configuring Power BI Desktop to work with R Configuring the Power BI service to work with R Installing the on-premises data gateway in personal mode Sharing reports that use R scripts in the Power BI service R visuals limitations Summary 3 Configuring Python with Power BI Technical requirements The available Python engines Choosing a Python engine to install The Python engines used by Power BI Installing the suggested Python engines Installing an IDE for Python development Configuring Python with RStudio Configuring Python with Visual Studio Code Configuring Power BI Desktop to work with Python Configuring the Power BI service to work with R Sharing reports that use Python scripts in the Power BI service Limitations of Python visuals Summary 4 Importing Unhandled Data Objects Technical requirements Importing RDS files in R A brief introduction to Tidyverse Creating a serialized R object Using an RDS file in Power BI Importing PKL files in Python A very short introduction to the PyData world Creating a serialized Python object Using a PKL file in Power BI Summary References 5 Using Regular Expressions in Power BI Technical requirements A brief introduction to regexes The basics of regexes Checking the validity of email addresses Checking the validity of dates Validating data using regex in Power BI Using regex in Power BI to validate emails with Python Using regex in Power BI to validate emails with R Using regex in Power BI to validate dates with Python Using regex in Power BI to validate dates with R Loading complex log files using regex in Power BI Apache access logs Importing Apache access logs in Power BI with Python Importing Apache access logs in Power BI with R Extracting values from text using regex in Power BI One regex to rule them all Using regex in Power BI to extract values with Python Using regex in Power BI to extract values with R Summary References 6 Anonymizing and Pseudonymizing your Data in Power BI Technical requirements De-identifying data De-identification techniques Understanding pseudonymization What is anonymization? Anonymizing data in Power BI Anonymizing data using Python Anonymizing data using R Pseudonymizing data in Power BI Pseudonymizing data using Python Pseudonymizing data using R Summary References 7 Logging Data from Power BI to External Sources Technical requirements Logging to CSV files Logging to CSV files with Python Logging to CSV files with R Logging to Excel files Logging to Excel files with Python Logging to Excel files with R Logging to an Azure SQL Server Installing SQL Server Express Creating an Azure SQL database Logging to an Azure SQL server with Python Logging to an Azure SQL server with R Summary References 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 Installing Dask on your laptop Creating a Dask DataFrame Extracting information from a Dask DataFrame Importing a large dataset in Power BI with Python Importing large datasets with R Installing disk.frame on your laptop Creating a disk.frame instance Extracting information from disk.frame Importing a large dataset in Power BI with R Summary References 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 Using an explicit GET request Using an explicit GET request in parallel Using the Geocoder library in parallel Geocoding addresses using R Using an explicit GET request Using an explicit GET request in parallel Using the tidygeocoder package in parallel Accessing web services using Power BI Geocoding addresses in Power BI with Python Geocoding addresses in Power BI with R Summary References 10 Calculating Columns Using Complex Algorithms Technical requirements The distance between two geographic locations Spherical trigonometry The law of Cosines distance The law of Haversines distance Vincenty’s distance What kind of distance to use and when Implementing distances using Python Calculating distances with Python Calculating distances in Power BI with Python Implementing distances using R Calculating distances with R Calculating distances in Power BI with R The basics of linear programming Linear equations and inequalities Formulating a linear optimization problem Definition of the LP problem to solve Formulating the LP problem Handling optimization problems with Python Solving the LP problem in Python Solving the LP problem in Power BI with Python Solving LP problems with R Solving the LP problem in R Solving the LP problem in Power BI with R Summary References 11 Adding Statistics Insights: Associations Technical requirements Exploring associations between variables Correlation between numeric variables Karl Pearson’s correlation coefficient Charles Spearman’s correlation coefficient Maurice Kendall’s correlation coefficient Description of a real case Implementing correlation coefficients in Python Implementing correlation coefficients in R Implementing correlation coefficients in Power BI with Python and R Correlation between categorical and numeric variables Considering both variables categorical Considering a numeric variable and a categorical one Implementing correlation coefficients in Python Implementing correlation coefficients in R Implementing correlation coefficients in Power BI with Python and R Summary References 12 Adding Statistics Insights: Outliers and Missing Values Technical requirements What outliers are and how to deal with them The causes of outliers Dealing with outliers Identifying outliers Univariate outliers Multivariate outliers Implementing outlier detection algorithms Implementing outlier detection in Python Implementing outlier detection in R Implementing outlier detection in Power BI What missing values are and how to deal with them The causes of missing values Handling missing values Diagnosing missing values in R and Python Implementing missing value imputation algorithms Removing missing values Imputing tabular data Imputing time-series data Imputing missing values in Power BI Summary References 13 Using Machine Learning without Premium or Embedded Capacity Technical requirements Interacting with ML in Power BI with data flows Using AutoML solutions PyCaret Azure AutoML RemixAutoML for R Embedding training code in Power Query Training and using ML models with PyCaret Using PyCaret in Power BI Using trained models in Power Query Scoring observations in Power Query using a trained PyCaret model Using trained models in Script Visuals Scoring observations in a script visual using a trained PyCaret model Calling web services in Power Query Using Azure AutoML models in Power Query Using cognitive services in Power Query Summary References 14 Exploratory Data Analysis Technical requirements What is the goal of EDA? Understanding your data Cleaning your data Discovering associations between variables Exploratory Data Analysis with Python and R Exploratory data analysis in Power BI Dataset summary page Missing values exploration Univariate exploration Multivariate exploration Variable associations Summary References 15 Advanced Visualizations Technical requirements Choosing a circular barplot Implementing a circular barplot in R Implementing a circular barplot in Power BI Summary References 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 Installing the pbiviz package Developing your first R HTML custom visual Importing the custom visual package into Power BI Summary References
Donate to keep this site alive
How to download source code?
1. Go to: https://github.com/PacktPublishing
2. In the Find a repository… box, search the book title: Extending Power BI with Python and R: Ingest, transform, enrich, and visualize data using the power of analytical languages
, sometime you may not get the results, please search the main title.
3. Click the book title in the search results.
3. Click Code to download.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.