Machine Learning in Power BI with R and Python (eng)

Machine Learning in Power BI with R and Python Advanced Analytics Predictive Analytics Machine Learning For everybody Release: November 2021 Pablo J Moreno First Edition Gabriel Gomez www.mlbi.io This book contributes to open source development and data literacy education

Machine Learning in Power BI with R and Python is written in plain language with multiple practical examples, allowing any Power BI user, amateur or expert, to perform Advanced Analytics or Machine Learning. Programming experience is not required. Section I describes all the foundational concepts about advanced analytics and machine learning. Section II is focused on developing advanced analytics using R, including custom visualization and advanced data transformation. In Section III a comprehensive guide of applied machine learning with Python is made to cover any scenario. Finally, in Section IV additional resources and material is provided to allow the reader to continue its journey in those disciplines. www.mlbi.io

Motivation and purpose of this book Power BI Power BI uso habitual Power BI is a well-known tool for reporting, data analysis and business intelligence, due to its functionality and rich ecosystem. However, it offers great possibilities when interacts with R and Python. The purpose of this book is to allow any user of Power BI to get closer to R and Python, when working within Power BI, so added value can be provided to analytics beyond historical data analysis. This is neither a book about programming, nor about data science per se, however, we cover the basics and foundations for implementation and interpretation of results. We hope that this book Will introduce the reader into data science to an advanced level. www.mlbi.io

TABLE OF CONTENTS Section I - Fundamentals Fundamentals of Business Intelligence, Advanced Analytics, Machine Learning, Data Science and Artificial Intelligence Business Intelligence Advanced Analytics Descriptive analytics Diagnostics analytics Predictive Analytics Machine Learning Regression models Classification models Clustering models Association models Control models Machine Learning Life Cycle Data Science Artificial Intelligence Data Roles Data analyst Data engineer Database administrator Machine Learning Engineer Data Scientist Data Architect Statistician Business Analyst Big Data Big Data versus Small Data Data Quality Tidy Data www.mlbi.io

Section II – Advanced Analytics with R Installing R, RStudio and sync up with Power BI Importing and Exporting Importing documents Import csv Import Excel Import any delimited document Import from ZIP (no decompression) Import from SPSS, STATA and SAS Import from MongoDB Import from Google Drive Exporting data (write-back) Export as csv Export as Google doc (Spread sheet) Write table at database (SQL, MySQL, PostgreSQL) Data wrangling and analysis Basic data wrangling Basic functions of dplyr Data transformation with dplyr Column title formatting Handling null values. Imputations Calculated columns Scaling and centering values Deviation over average Percent over total Cumulative sum Line break Visualization Understanding ggplot2 Components Aesthetics Geometries Scales Facets Graphic rules Examples of advanced visualization www.mlbi.io

Ridge or Area chart Ridge stepped chart Violin and box chart Multiple regression chart Box-cox and jitter chart Correlation (multiple variables) Correlogram Composed Correlogram Marginal distribution Clustering or Dendrogram Doble Dendrogram Density chart Multiple circular bar chart Parallel Coordinates Time Serie forecasting chart (prophet) Lollipop chart Annotations Conditional format Area color on line chart Advanced analytics Time Series and Forecasting Time Series object Statistical methods Naive Method Simple Exponential Smooth Holt Trend ARIMA TBATS Prophet Lineal Regression Simple Lineal regression Multiple Lineal regression No-Lineal regression, monotonic and no-monotonic Generalized additive model (gam) Logistic Regression Putting a R predictive model in production with Power BI www.mlbi.io

Section III – Machine Learning with Python Installing Python and sync up with Power BI Installing Anaconda and Python Setting up a virtual environment Installing packages Sync’ing up Python with Power BI Machine Learning with Python Unsupervised Learning Clustering Implementation of clustering Application – customer segmentation Application – geospatial analysis Association, Recommendation and Market Basket Analysis Implementation of association Application – Market basket analysis Application – IT helpdesk requests Anomaly detection Implementation of anomaly detection Application – Industrial production quality control Application – Telemetry analysis Application – P-Card purchase analysis Supervised Learning Regression Analysis Implementation of regression analysis Structuring the prediction process Application – Variance analysis Application – Machine Learning with regression Application – Time Series as regression Classification Analysis Implementation of classification analysis Structuring the prediction process Application – Bi-variable classification Application – Multivariable classification Application – Unbalanced bi-variable classification www.mlbi.io

Model evaluation and result validation Selecting the optimal model Regression Classification Criteria to select the right model Result validation Regression Classification Application – validation of results – regression Natural Language Processing Text analysis Word processing Text cleaning Sentiment analysis Production Create an app in Power BI Regression method Classification method Model in production Section IV – Additional resources Model evaluation methods - definitions Terms Ggplot2 – cheat sheet Tidyverse – cheat sheet Additional resources Bibliography www.mlbi.io

Acknowledgments To Almighty God. To our family, ancestors, and beloved friends. To our professional Friends and colleagues that we have met during our lives, with special mention to: Freddy González Moez Ali Pablo Peralta Francisco Mullor Ricardo Rincón Ruben Pertusa Miguel Egea Ana Maria Bisbé Salvador Ramos Didier Atehortúa And so many other individuals who impacted our personal and professional lives. www.mlbi.io

About the authors Pablo J Moreno Data Scientist with experience in Business Intelligence, Advanced Analytics, and applied Machine Learning in multiple fields, such as financial markets, corporate Finance, digital marketing, business operations, risk management and internal control. Microsoft MVP (Data Platform) since 2019 Gabriel Gomez IT professional specialized in software Development and database administration and business Intelligence. Expert in data mining and advanced Analytics and custom reporting. Master in Cybersecurity www.mlbi.io

Publication: November 2021 mlbi.io www.mlbi.io