Luca Zavarella - Extending Power BI with Python and R: Ingest, transform, enrich, and visualize data using the power of analytical languages

Copyright 2021 Packt Publishing

This is an Early Access product. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the content and extracts of this book may evolve as it is being developed to ensure it is up-to-date.

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, ortransmitted in any form or by any means, without the prior written permission of the publisher,except in the case of brief quotations embedded in critical articles or reviews.

The information contained in this book is sold without warranty, either express or implied. Neither the author nor Packt Publishing or its dealers and distributors will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Early Access Publication: Extending Power BI with Python and R

Early Access Production Reference: B17081

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham

B3 2PB, UK

ISBN: 978-1-80107-820-7

Welcome to Packt Early Access. Were giving you an exclusive preview of this book before it goes on sale. It can take many months to write a book, but our authors have cutting-edge information to share with you today. Early Access gives you an insight into the latest developments by making chapter drafts available. The chapters may be a little rough around the edges right now, but our authors will update them over time. Youll be notified when a new version is ready.

This title is in development, with more chapters still to be written, which means you have the opportunity to have your say about the content. We want to publish books that provide useful information to you and other customers, so well send questionnaires out to you regularly. All feedback is helpful, so please be open about your thoughts and opinions. Our editors will work their magic on the text of the book, so wed like your input on the technical elements and your experience as a reader. Well also provide frequent updates on how our authors have changed their chapters based on your feedback.

You can dip in and out of this book or follow along from start to finish; Early Access is designed to be flexible. We hope you enjoy getting to know more about the process of writing a Packt book. Join the exploration of new topics by contributing your ideas and see them come to life in print.

1. Where and How to Use R and Python Scripts in Power BI
2. Configuring R with Power BI
3. Configuring Python with Power BI
4. Importing Unhandled Data Objects
5. Using Regular Expressions in Power BI
6. Anonymizing and Pseudonymizing Your Data in Power BI
7. Logging Data From Power BI To External Sources
8. Loading Large Datasets Beyond the Available RAM in Power BI
9. Calling External APIs to Enrich Your Data
10. Calculating Columns Using Complex Algorithms
11. Adding Statistics Insights: Associations
12. Adding Statistics Insights: Outliers and Missing Values
13. Using Machine Learning Without Premium or Embedded Capacity
14. Exploratory Data Analysis
15. Advanced Visualizations
16. Interactive R Custom Visuals

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) and Azure Analysis Services. It also 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 analytic 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
• R and Python limitations on Power BI products

This chapter requires you to have Power BI Desktop already installed on your machine (you can download it from here: https://aka.ms/pbiSingleInstaller).

In this first section, the 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.

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 are the most commonly used connectors 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

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: