Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying Extending Power BI with Python and R
  • Table Of Contents Toc
Extending Power BI with Python and R

Extending Power BI with Python and R - Second Edition

By : Zavarella
5 (30)
Buy this Book
close
close
Extending Power BI with Python and R

Extending Power BI with Python and R

5 (30)
By: Zavarella
Buy this Book

Overview of this book

The latest edition of this book delves deep into advanced analytics, focusing on enhancing Python and R proficiency within Power BI. New chapters cover optimizing Python and R settings, utilizing Intel's Math Kernel Library (MKL) for performance boosts, and addressing integration challenges. Techniques for managing large datasets beyond available RAM, employing the Parquet data format, and advanced fuzzy matching algorithms are explored. Additionally, it discusses leveraging SQL Server Language Extensions to overcome traditional Python and R limitations in Power BI. It also helps in crafting sophisticated visualizations using the Grammar of Graphics in both R and Python. This Power BI book will help you master data validation with regular expressions, import data from diverse sources, and apply advanced algorithms for transformation. You'll learn how to safeguard personal data in Power BI with techniques like pseudonymization, anonymization, and data masking. You'll also get to grips with the key statistical features of datasets by plotting multiple visual graphs in the process of building a machine learning model. The book will guide you on utilizing external APIs for enrichment, enhancing I/O performance, and leveraging Python and R for analysis. You'll reinforce your learning with questions at the end of each chapter.
Table of Contents (27 chapters)
close
close
close
Preface
Icon
Preface
Icon
Who this book is for
Icon
What this book covers
Icon
Software used in this book
Icon
To get the most out of this book
Icon
Get in touch
Lock Free Chapter
1
Where and How to Use R and Python Scripts in Power BI
chevron up
Icon
Where and How to Use R and Python Scripts in Power BI
Icon
Technical requirements
Icon
Injecting R or Python scripts into Power BI
Icon
Using R and Python to interact with your data
Icon
Python and R compatibility across Power BI products
Icon
Summary
Icon
Test your knowledge
2
Configuring R with Power BI
Icon
Configuring R with Power BI
Icon
Technical requirements
Icon
The available R engines
Icon
Choosing an R engine to install
Icon
Installing an IDE for R development
Icon
Configuring Power BI Desktop to work with R
Icon
Configuring the Power BI service to work with R
Icon
R script visuals limitations
Icon
Summary
Icon
Test your knowledge
3
Configuring Python with Power BI
Icon
Configuring Python with Power BI
Icon
Technical requirements
Icon
The available Python engines
Icon
Creating an environment for data transformations using pip
Icon
Creating an optimized environment for data transformations using conda
Icon
Creating an environment for Python script visuals on the Power BI service
Icon
What to do when the Power BI service upgrades the Python engine
Icon
Installing an IDE for Python development
Icon
Configuring Power BI Desktop to work with Python
Icon
Limitations of Python visuals
Icon
Summary
Icon
Test your knowledge
4
Solving Common Issues When Using Python and R in Power BI
Icon
Solving Common Issues When Using Python and R in Power BI
Icon
Technical requirements
Icon
Avoiding the ADO.NET error when running a Python script in Power BI
Icon
Avoiding the Formula.Firewall error
Icon
Using multiple datasets in Python and R script steps
Icon
Dealing with dates/times in Python and R script steps
Icon
Summary
Icon
Test your knowledge
5
Importing Unhandled Data Objects
Icon
Importing Unhandled Data Objects
Icon
Technical requirements
Icon
Importing RDS files in R
Icon
Importing PKL files in Python
Icon
Summary
Icon
References
Icon
Test your knowledge
6
Using Regular Expressions in Power BI
Icon
Using Regular Expressions in Power BI
Icon
Technical requirements
Icon
A brief introduction to regexes
Icon
Validating data using regex in Power BI
Icon
Loading complex log files using regex in Power BI
Icon
Extracting values from text using regex in Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
7
Anonymizing and Pseudonymizing Your Data in Power BI
Icon
Anonymizing and Pseudonymizing Your Data in Power BI
Icon
Technical requirements
Icon
De-identifying data
Icon
Anonymizing data in Power BI
Icon
Pseudonymizing data in Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
8
Logging Data from Power BI to External Sources
Icon
Logging Data from Power BI to External Sources
Icon
Technical requirements
Icon
Logging to CSV files
Icon
Logging to Excel files
Icon
Logging to (Azure) SQL Server
Icon
Summary
Icon
References
Icon
Test your knowledge
9
Loading Large Datasets Beyond the Available RAM in Power BI
Icon
Loading Large Datasets Beyond the Available RAM in Power BI
Icon
Technical requirements
Icon
A typical analytic scenario using large datasets
Icon
Importing large datasets with Python
Icon
Importing large datasets with R
Icon
Summary
Icon
References
Icon
Test your knowledge
10
Boosting Data Loading Speed in Power BI with Parquet Format
Icon
Boosting Data Loading Speed in Power BI with Parquet Format
Icon
Technical requirements
Icon
From CSV to the Parquet file format
Icon
Using the Parquet format to speed up a Power BI report
Icon
Summary
Icon
References
Icon
Test your knowledge
11
Calling External APIs to Enrich Your Data
Icon
Calling External APIs to Enrich Your Data
Icon
Technical requirements
Icon
What is a web service?
Icon
Registering for Bing Maps web services
Icon
Geocoding addresses using Python
Icon
Geocoding addresses using R
Icon
Accessing web services using Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
12
Calculating Columns Using Complex Algorithms: Distances
Icon
Calculating Columns Using Complex Algorithms: Distances
Icon
Technical requirements
Icon
What is a distance?
Icon
The distance between two geographic locations
Icon
The distance between two strings
Icon
Summary
Icon
References
Icon
Test your knowledge
13
Calculating Columns Using Complex Algorithms: Fuzzy Matching
Icon
Calculating Columns Using Complex Algorithms: Fuzzy Matching
Icon
Technical requirements
Icon
Exploring default fuzzy matching in Power BI
Icon
Introducing probabilistic record linkage algorithms
Icon
Applying probabilistic record linkage algorithms
Icon
Summary
Icon
References
Icon
Test your knowledge
14
Calculating Columns Using Complex Algorithms: Optimization Problems
Icon
Calculating Columns Using Complex Algorithms: Optimization Problems
Icon
Technical requirements
Icon
The basics of linear programming
Icon
Handling optimization problems with Python and R
Icon
Summary
Icon
References
Icon
Test your knowledge
15
Adding Statistical Insights: Associations
Icon
Adding Statistical Insights: Associations
Icon
Technical requirements
Icon
Exploring associations between variables
Icon
Correlation between numeric variables
Icon
Correlation between non-numeric variables
Icon
Correlation between non-numeric and numeric variables
Icon
Summary
Icon
References
Icon
Test your knowledge
16
Adding Statistical Insights: Outliers and Missing Values
Icon
Adding Statistical Insights: Outliers and Missing Values
Icon
Technical requirements
Icon
What outliers are
Icon
The causes of outliers
Icon
Identifying outliers
Icon
Dealing with outliers
Icon
Implementing outlier detection algorithms
Icon
What missing values are and how to deal with them
Icon
Diagnosing missing values in R and Python
Icon
Implementing missing value imputation algorithms
Icon
Summary
Icon
References
Icon
Test your knowledge
17
Using Machine Learning without Premium or Embedded Capacity
Icon
Using Machine Learning without Premium or Embedded Capacity
Icon
Technical requirements
Icon
Interacting with ML in Power BI with dataflows
Icon
Using AutoML solutions
Icon
Embedding training code in Power Query
Icon
Using trained models in Power Query
Icon
Using trained models in script visuals
Icon
Calling web services in Power Query
Icon
Summary
Icon
References
Icon
Test your knowledge
18
Using SQL Server External Languages for Advanced Analytics and ML Integration in Power BI
Icon
Using SQL Server External Languages for Advanced Analytics and ML Integration in Power BI
Icon
Technical requirements
Icon
Introducing SQL Server Machine Learning Services
Icon
Installing Python and R custom runtimes for SQL Server
Icon
The need for external languages with Power BI
Icon
Using external languages with Power BI
Icon
Summary
Icon
References
Icon
Acknowledgements
Icon
Test your knowledge
19
Exploratory Data Analysis
Icon
Exploratory Data Analysis
Icon
Technical requirements
Icon
What is the goal of EDA?
Icon
Understanding your data
Icon
EDA with Python and R
Icon
EDA in Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
20
Using the Grammar of Graphics in Python with plotnine
Icon
Using the Grammar of Graphics in Python with plotnine
Icon
Technical requirements
Icon
What is plotnine?
Icon
Analyzing Titanic data with plotnine
Icon
Using plotnine in Power BI
Icon
Summary
Icon
References
Icon
Acknowledgment
Icon
Test your knowledge
21
Advanced Visualizations
Icon
Advanced Visualizations
Icon
Technical requirements
Icon
Choosing a circular barplot
Icon
Implementing a circular barplot in R
Icon
Implementing a circular barplot in Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
22
Interactive R Custom Visuals
Icon
Interactive R Custom Visuals
Icon
Technical requirements
Icon
Why interactive R custom visuals?
Icon
Adding a dash of interactivity with Plotly
Icon
Exploiting the interactivity provided by HTML widgets
Icon
Packaging it all into a Power BI custom visual
Icon
Importing the custom visual package into Power BI
Icon
Summary
Icon
References
Icon
Test your knowledge
23
Other Books You May Enjoy
Icon
Other Books You May Enjoy
24
Index
Icon
Index
1
Appendix 1: Answers
Icon
Appendix 1: Answers
2
Appendix 2: Glossary
Icon
Appendix 2: Glossary

Where and How to Use R and Python Scripts in Power BI

Power BI is Microsoft’s flagship self-service business intelligence product. It consists of a set of on-premises applications and cloud-based services that help organizations integrate, transform, and analyze data from a wide variety of source systems through a user-friendly interface.

The platform is not limited to data visualization. Power BI is much more than this when you consider that its analytics engine (Vertipaq) is the same as SQL Server Analysis Services (SSAS), Azure Analysis Services, and Power Pivot in Excel and it is also the engine used for reports and datasets published to the Power BI service. In addition, it uses Power Query as its data extraction and transformation engine, which we find in both Analysis Services and Excel. The engine comes with a very powerful and versatile formula language (M) and GUI, thanks to which you can “grind” and shape any type of data into any form.

Moreover, Power BI supports DAX as a data analytics formula language, which can be used for advanced calculations and queries on data that has already been loaded into tabular data models.

Such a versatile and powerful tool is a godsend for anyone who needs to do data ingestion and transformation in order to build dashboards and reports to summarize a company’s business.

Recently, the availability of huge amounts of data, along with the ability to scale the computational power of machines, has made the area of advanced analytics more appealing. So, new mathematical and statistical tools have become necessary in order to provide rich insights. Hence the integration of analytical languages such as Python and R within Power BI.

R or Python scripts can only be used within Power BI with specific features. Knowing which Power BI tools can be used to inject R or Python scripts into Power BI is key to understanding whether the problem you want to address is achievable with these analytical languages.

This chapter will cover the following topics:

  • Injecting R or Python scripts into Power BI
  • Using R and Python to interact with your data
  • Python and R compatibility across Power BI products
CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
Extending Power BI with Python and R
End of Section 1
Next Section
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options
font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon