Advanced Analytics in Power BI with R and Python: Ingesting, Transforming, Visualizing

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Part I: Creating Custom Data Visualizations Using R
Chapter 1: The Grammar of Graphics
Steps to build an R custom visual in Power BI
Step 1: Configure Power BI
Step 2: Drag the “R custom visual” icon to the Power BI canvas
Step 3: Define the data set
Step 4: Develop the visual in your default R IDE
Step 5: Use the following template to develop your visual
Step 6: Make the script functional
Recommended steps to create an R visual using ggplot2
Step 1: Load the required packages that you will need for your script
Step 2: Make any required adjustments to the data set
Step 3: Initiate the creation of the visualization with the ggplot() function
Step 4: Add desired geom(s)
Step 5: Define your titles, subtitles, and caption
Step 6: Make any necessary changes to the x and y axis
Step 7: Apply themes if needed
Step 8: Use the theme() function to change any specific non-data elements
Bonus step: Specifying specific colors for your points in your scatter plot
The importance of having “tidy” data
Popular geoms
Controlling aesthetics with scales
Themes built into ggplot2
Using R visuals in the Power BI service
Helper packages for ggplot2
Summary
Chapter 2: Creating R Custom Visuals in Power BI Using ggplot2
Callout chart
Step 1: Acquire the necessary data
Step 2: Create a slicer based on the year in the Filter pane
Step 3: Configure the R visual in Power BI
Step 4: Export data to R Studio for development
Step 5: Load the required packages
Step 6: Create the variables needed for the data validation test
Step 7: Create the data validation test
Step 8: Add additional columns to your data set that is required for the custom R visual
Step 9: Create the variables that will be used for the dynamic portions of the chart
Step 10: Start building the chart by defining the ggplot() function
Step 11: Add a column chart layer to the R visual
Step 12: Add a text layer to the R visuals
Step 13: Modify the y axis
Step 14: Convert chart from a vertical column chart to horizontal bar chart
Step 15: Add a dynamic annotation to the R visuals
Step 16: Add the dynamic titles and caption to the  R visual
Step 17: Remove labels from x axis and y axis
Step 18: Remove legend
Step 19: Change the look and feel of the visual using theme_few()
Step 20: Center align titles
Step 21: Add code to Power BI
Bubble chart
Step 1: Acquire the necessary data
Step 2: Load the data into Power BI
Step 3: Create a filter slicer based on the year
Step 4: Do the initial R visual configuration
Step 5: Export data to R Studio for development
Step 6: Load the required packages
Step 7: Create the variables needed for the data validation test
Step 8: Create the data validation test
Step 9: Define the colors for the conferences and conference divisions
Step 10: Dynamically define the chart titles
Step 11: Create the chart’s data set
Step 12: Start the chart by defining the ggplot function
Step 13: Add the layer for your bubble chart using the geom_point geom
Step 14: Add labels to the bubble chart
Step 15: Change the color of the bubble’s border and change the color of the bubble’s fill
Step 16: Create the ggtitle
Step 17: Set the theme
Step 18: Add code to Power BI
Forecast
Step 1: Acquire the necessary data
Step 2: Create a slicer based on quarterback
Step 3: Configure the R visual
Step 4: Export data to R Studio for development
Step 5: Load the required packages to the script
Step 6: Create the variables needed for the data validation test
Step 7: Create the data validation test
Step 8: Create the dynamic chart title
Step 9: Create the data set that is needed to generate the forecast
Step 10: Generate the forecast
Step 11: Generate the plot
Step 12: Add code to Power BI
Line chart with shade
Step 1: Acquire the necessary data
Step 2: Load the data into Power BI
Step 3: Create the report slicers
Step 4: Configure the R visual
Step 5: Export data to R Studio for development
Step 6: Load the required packages to the script
Step 7: Create the variables needed for the data validation test
Step 8: Create the data validation test
Step 9: Create a new data frame based on the dataset data frame
Step 10: Create the variables that will be used for the dynamic portions of the chart
Step 11: Create the data sets needed for background shade
Step 12: Create the data sets needed for line chart
Step 13: Create a named character vector that will be used to color the shades
Step 14: Start the chart by defining the ggplot function
Step 15: Add a layer to create the background shade
Step 16: Add a line chart based on the statistic selected
Step 17: Reshade the background using pre-determined colors based on the political party
Step 18: Format the y axis based on the statistic selected
Step 19: Add labels to the x and y axis
Step 20: Add the dynamic titles and caption to the custom R visuals
Step 21: Apply a theme based on The Economists publication
Step 22: Add code to Power BI
Map
Step 1: Acquire the necessary data
Step 2: Load the data into Power BI
Step 3: Create a slicer based on state in the Filter pane
Step 4: Configure the R visual
Step 5: Export data to R Studio for development
Step 6: Load the required packages
Step 7: Create the variables needed for the data validation test
Step 8: Create the data validation test
Step 9: Create the variables for the chart titles
Step 10: Add the quintile column to the data set
Step 11: Define the colors that will be used to shade the map
Step 12: Define the ggplot() function
Step 13: Add the map layer
Step 14: Format the x and y axis
Step 15: Color the counties based on their quintile
Step 16: Improve the approximation of the selected state
Step 17: Add the dynamic titles and caption to the custom R visuals
Step 18: Apply the theme_map() theme
Step 19: Add code to Power BI
Quad chart
Step 1: Acquire the necessary data
Step 2: Load the data into Power BI
Step 3: Create a slicer for game type and period
Step 4: Configure an R visual on the report canvas
Step 5: Export data to R Studio for development
Step 6: Load the required packages
Step 7: Create the variables needed for the data validation test
Step 8: Create the data validation test
Step 9: Create the chart titles
Step 10: Add additional columns to the data set
Step 11: Start the chart by defining the ggplot function
Step 12: Use the geom_point() geom to plot the players on the plot
Step 13: Add the labels for each quadrant
Step 14: Draw vertical and horizontal lines through the x and y axis
Step 15: Add quadrant labels to the chart
Step 16: Add labels for the x and y axis
Step 17: Add the dynamic titles and caption to the custom R visual
Step 18: Add a theme
Step 19: Perform last minute cleanup
Step 20: Add code to Power BI
Adding regression line
Step 1: Acquire the necessary data
Step 2: Load the data into Power BI
Step 3: Configure the R visual
Step 4: Export data to R Studio for development
Step 5: Load the required packages
Step 6: Create the variables needed for the data validation test
Step 7: Create the data validation test
Step 8: Start the chart by initializing the ggplot function
Step 9: Add a scatter plot layer to the R visual
Step 10: Add regression line layer to the R visuals
Step 11: Add a title to the chart
Step 12: Change the chart’s theme
Step 13: Perform some last minute cleanup
Step 14: Add code to Power BI
Part II: Ingesting Data into the Power BI Data Model Using R and Python
Chapter 3: Reading CSV Files
Dynamically combining files
Example scenario
Picking the rolling 24 months using R
Step 1: Load the required R packages for the script
Step 2: Change your working directory to the folder that contains the Sales data sets
Step 3: Read the file names into a character vector
Step 4: Create a date vector
Step 5: Create a data frame using the two vectors
Step 6: Get the upper and lower bound of our desired date range
Step 7: Subset the data frame to only include the desired months
Step 8: Create a data frame that is based on the union of all the files
Step 9: Combine the code into one script and paste into the R editor for Power BI
Picking the rolling 24 months using Python
Step 1: Create a Python script and load the necessary libraries
Step 2: Change your working directory to the Python_Code folder
Step 3: Read the file names into a Python list
Step 4: Create a pandas data frame that will hold the information of the files to combine
Step 5: Create a new column that strips the date information from the monthly_reports column
Step 6: Get the upper and lower bound of the date range
Step 7: Subset the data frame
Step 8: Combine files into one data frame
Step 9: Add the code to Power BI
Filtering rows based on a regular expression
Leveraging regular expressions via R
Step 1: Load the required packages
Step 2: Load the file that contains the potential voters into R
Step 3: Define the regular expression
Step 4: Remove the bad email addresses from the data set
Step 5: Combine the preceding code into one script and paste in the R editor for Power BI
Leveraging regular expressions via Python
Step 1: Load the required libraries
Step 2: Load the file that contains the potential voters into Python and assign the contents to a pandas data frame
Step 3: Define the regular expression that matches the pattern of a well-formed email address
Step 4: Remove the bad email addresses from the data set
Step 5: Combine the preceding code into one script and paste in the Python editor for Power BI
Chapter 4: Reading Excel Files
Reading Excel files using R
Step 1: Import the tidyverse and readxl package
Step 2: Create the shell of the combine_sheets function
Step 3: Get the name of the sheets you need to combine in your function from the specified Excel workbook
Step 4: Convert the character vector built in Step 3 to a named character vector
Step 5: Use the mapr_df function to combine the sheets into a single data frame
Step 6: Return the data frame
Step 7: Set the working directory to the location where the Excel files are located
Step 8: Assign the file names to the excel_file_paths variable
Step 9: Use the map_dfr function to apply the combine_sheets function to each file in the working directory
Step 10: Copy the R script and paste it into the R editor via GetData in Power BI
Reading Excel files using Python
Step 1: Import the os and pandas library
Step 2: Create the shell of the combine_sheets function
Step 3: Create an Excel object based on the  workbook located at the path specified in the  excel_file_path variable
Step 4: Create a list of the sheet names in the workbook specified by the excel_file_path variable
Step 5: Use the read_excel method of pandas to read the data from each sheet into one data frame
Step 6: Return the data frame held in df as the output from the combine_sheets function
Step 7: Set the working directory to the location that contains the Excel data you want to combine
Step 8: Get the list of the file names in the current working directory and assign it to the excel_file_paths variable
Step 9: Create an empty data frame and name it combined_workbooks
Step 10: Create the shell of the for loop
Step 11: Combine all the data in each sheet into one data frame using the combine_sheets function
Step 12: Append the combined_workbook data frame to the combined_workbooks data frame
Step 13: Copy the Python script and paste it into the Python editor via GetData in Power BI
Chapter 5: Reading SQL Server Data
Adding AdventureWorksDW_StarSchema database to your SQL Server instance
Reading SQL Server data into Power BI using R
Step 1: Create a DSN to the SQL Server database
Step 2: Create a log table in SQL Server
Step 3: Start developing the R script to load DimDate
Step 4: Create a variable to hold the name of the table you want to import
Step 5: Create a variable to hold the sql statement that will be used to return the table
Step 6: Create a connection to SQL Server
Step 7: Retrieve the data from SQL Server and store it in a data frame
Step 8: Get the current time
Step 9: Get the number of records that were read
Step 10: Create a one record R data frame that contains the information you want to log
Step 11: Insert the information you gathered in Step 10 into the history log table
Step 12: Close your connection
Step 13: Copy the script into Power BI
Step 14: Create a script to load DimProduct based on the ReadLog_DimDate.R script
Step 15: Create a script to load DimPromotion based on the ReadLog_DimDate.R script
Step 16: Create a script to load DimSalesTerritory based on the ReadLog_DimDate.R script
Step 17: Create a script to load FactInternetSales based on the ReadLog_DimDate.R script
Reading SQL Server data using Python
Step 1: Create a DSN to the SQL Server database
Step 2: Create a log table in SQL Server
Step 3: Begin creating the script to load the  DimDate table
Step 4: Create a variable that holds the name of the table that you want to read into Power BI
Step 5: Create a connection to the database using sqlalchemy
Step 6: Read in the contents of the DimDate table and store it as a data frame in the df_read variable
Step 7: Get the current date and time and store the information into the datastamp variable
Step 8: Calculate the number of records in the  DimDate table
Step 9: Create a one record pandas data frame that contains the information you want to log
Step 10: Insert the information you gathered in Step 9 into the history log table
Step 11: Copy the script into Power BI
Step 12: Create a script to load DimProduct based on the ReadLog_DimDate.py script
Step 13: Create a script to load DimPromotion based on the ReadLog_DimDate.py script
Step 14: Create a script to load DimSalesTerritory based on the ReadLog_DimDate.py script
Step 15: Create a script to load FactInternetSales based on the ReadLog_DimDate.py script
Chapter 6: Reading Data into the Power BI Data Model via an API
Reading Census data into Power BI via an  API using R
Step 1: Get a personal Census API key
Step 2: Load the necessary R packages
Step 3: Identify the variables you want to return from your data set
Step 4: Create a character vector of the tables that contains the variables you want to return
Step 5: Configure the get_acs function
Step 6: Give the variables (columns) meaningful names
Step 7: Copy the script into Power BI
Reading Census data into Power BI via an API using Python
Step 1: Get a personal Census API key
Step 2: Load the necessary Python libraries
Step 3: Identify the variables you want to return in your data set
Step 4: Create a variable that is based on the list variables you want
Step 5: Create a list of tuples that contains the geographies you want to in your data set
Step 6: Retrieve the data using the censusdata.download() function
Step 7: Reset the index of the data frame created in  Step 6
Step 8: Define the new column names
Step 9: Rename columns
Step 10: Copy the script into Power BI
Summary
Part III: Transforming Data Using R and Python
Chapter 7: Advanced String Manipulation and Pattern Matching
Masking sensitive data
Masking sensitive data in Power BI using R
Step 1: Import tidyverse and stringr
Step 2: Create the scrub data function
Step 3: Read the comments into a data frame
Step 4: Mask the phone numbers and ssn numbers in the comment field
Step 5: Copy the script into Power BI
Masking sensitive data in Power BI using Python
Step 1: Import pandas, os, and re library
Step 2: Create the mask_text function
Step 3: Set the working directory
Step 4: Read the comments into a data frame
Step 5: Mask the phone numbers and SSNs
Step 6: Copy the script into Power BI
Counting the number of words and sentences in reviews
Counting the number of words and sentences in reviews in Power BI using R
Step 1: Import tidyverse and stringr
Step 2: Change working directory to location of the file
Step 3: Read in the Yelp data
Step 4: Subset the columns
Step 5: Add word count and sentence count columns
Step 6: Copy the script into Power BI
Counting the number of words in reviews in Power BI using Python
Step 1: Import pandas and os
Step 2: Change working directory to the location of the file
Step 3: Read the Yelp data into Python
Step 4: Create the word_count column
Step 5: Copy the script into Power BI
Removing names that are in an invalid format
Removing names that are in an invalid format in Power BI using R
Step 1: Import tidyverse and stringr
Step 2: Change working directory to location where the DimEmployee.csv file is
Step 3: Create a regular expression to match a valid name
Step 4: Read the data into a data frame
Step 5: Do an inline update of the Name column
Step 6: Copy the script into Power BI
Removing names that are in an invalid format in  Power BI using Python
Step 1: Import pandas, re, and os library
Step 2: Change working directory to the location where the DimEmployee csv file is
Step 3: Read in the DImEmployee data into an R data frame
Step 4: Create a regular expression that matches a valid name
Step 5: Compile the regular expression
Step 6: Define a function that executes the name test
Step 7: Apply the function to the column to scrub the name
Step 8: Copy the script into Power BI
Identifying patterns in strings based on  conditional logic
Identifying patterns in strings based on conditional logic in Power BI using R
Step 1: Import the tidyverse and stringr packages
Step 2: Change the working directory
Step 3: Create a function that identifies the products
Step 4: Read in the data from the ProductionOrders.csv file into an R data frame
Step 5: Add a column to the df named “Monitored Products”
Step 6: Copy the script into Power BI
Identifying patterns in strings based on conditional logic in Power BI using Python
Step 1: Import pandas, re, and os library
Step 2: Change working directory
Step 3: Compile the required regular expression
Step 4: Define a function that returns the monitored products
Step 5: Read in the data into Pandas data frame
Step 6: Create a new column named “Monitored Products”
Step 7: Copy the script into Power BI
Summary
Chapter 8: Calculated Columns Using R and Python
Create a Google Geocoding API key
Step 1: Log into the Google console
Step 2: Set up a billing account
Step 3: Add a new project
Step 4: Enable Geocoding API
Geocode the addresses using R
Geocode the addresses using Python
Calculate the distance with a custom function using R
Calculate the distance with a custom function using Python
Calculate distance with a pre-built function in Power BI using R
Calculate distance with a pre-built function in Power BI using Python
Summary
Part IV: Machine Learning and AI in Power BI Using R and Python
Chapter 9: Applying Machine Learning and AI to Your Power BI Data Models
Apply machine learning to a data set before bringing it into the Power BI data model
Predicting home values using R
Step 1: Have the data scientist share the model with you
Step 2: Load the tidyverse package
Step 3: Load the model object and the data set to be scored
Step 4: Subset the data frame so that it only contains the columns needed for the model
Step 5: Apply the model to your data set to predict the median home values
Step 6: Add the predictions to the original data set
Step 7: Copy the entire R script into the Power BI data model
Predicting home values using Python
Step 1: Have the data scientist share the model with you
Step 2: Load the necessary Python libraries needed for the script
Step 3: Load the model object and the Boston homes data set
Step 4: Extract the information needed from the bost_housing data frame
Step 5: Apply the model to the preprocessed data to predict the median home values
Step 6: Add the predictions to the original data set
Step 7: Copy the entire Python script into the Python script editor in Power BI
Using pre-built AI models to enhance Power BI data models
Set up Cognitive Services in Azure
The Data Science Virtual Machine (DSVM)
Performing sentiment analysis in Microsoft Cognitive Services via Python
Step 1: Get the Yelp review data from Kaggle
Step 2: Import the necessary libraries, modules, and function for the script
Step 3: Assign values to the variables used in the script
Step 4: Read in the sample of the Yelp review data into Python
Step 5: Transform the data frame to the format that is required by Microsoft Cognitive Services
Step 6: Score the reviews using the sentiment method of the text_analytics object
Step 7: Create a data frame to hold the sentiment data
Step 8: Add the data to Power BI
Applying AI to your Power BI data model using services other than Microsoft Cognitive Services
Configuring the Tone Analyzer service in IBM Watson
Step 1: Sign up for IBM Cloud account
Step 2: Log into the IBM Cloud
Step 3: Go to the Tone Analyzer page
Step 4: Define your Tone Analyzer service
Step 5: Get your API key
Writing the Python script to perform the tone analysis
Step 1: Import the required libraries and modules
Step 2: Create an instance of the IAMAuthenticator class
Step 3: Create an instance of the ToneAnalyzerV3 class
Step 4: Set the service URL of the service object
Step 5: Create a data frame that will hold the data you want to use for the tone analysis
Step 6: Create a data frame based on the scored data
Step 7: Create a looping structure to individually send the documents to IBM Watson
Step 8: Format and score the document
Step 9: Assign the results of the tone analysis and set the initial values of the tone variables
Step 10: Loop through the returned tones and assign their scores to the appropriate variable
Step 11: Create a data frame based on the listReturnedUtterance list
Step 12: Merge the dfReturnedUtterance data frame with the dfDocuments data frame
Step 13: Copy the complete script into Power BI
Chapter 10: Productionizing Data Science Models and Data Wrangling Scripts
Predicting home values in Power BI using R in SQL Server Machine Learning Services
Build the R script that adds the model to SQL Server
Step 1: Load the necessary packages
Step 2: Load the model into the R session
Step 3: Create a connection to the database
Step 4: Define the model variables
Step 5: Build the T-SQL statement to add the model to the database
Step 6: Add the code needed to execute the T-SQL statement from R
Step 7: Save the script
Use SQL Server Machine Learning Services with R to score the data
Step 1: Launch SQL Server Management Studio
Step 2: Create a connection to the server you want to use
Step 3: Add the BostonHousingInfo database to your server
Step 4: Add the model to the database
Step 5: Add the stored procedure to the database that will do the scoring
Step 6: Fetch the scored data in Power BI from SQL Server
Predicting home values in Power BI using Python in SQL Server Machine Learning Services
Create the script needed to add Python model to SQL Server
Step 1: Get the version of libraries used in this exercise
Step 2: Create a conda environment
Step 3: Create the code that pushes the model to SQL Server
Use SQL Server Machine Learning Services with Python from Power BI to score the data
Step 1: Launch SQL Server Management Studio
Step 2: Create a connection to the server you want to use
Step 3: Add the BostonHousingInfo database to your server
Step 4: Add the model to the database
Step 5: Add the stored procedure to the database that will do the scoring
Step 6: Fetch the scored data in Power BI from SQL Server
Performing sentiment analysis in Power BI using R in SQL Server Machine Learning Services
Add pre-built R models to SQL Server Machine Learning Services using PowerShell
Step 1: Check to see if the pre-trained models are installed
Step 2: Open PowerShell as administrator
Step 3: Download PowerShell script
Step 4: Run the downloaded script in PowerShell
Troubleshooting
Use pre-built R sentiment model in SQL Server Machine Learning Services to score data in Power BI
Step 1: Begin defining the stored procedures
Step 2: Define variables
Step 3: Set @Query variable
Step 4: Set @RScript variable
Step 5: Configure sp_execute_external_script
Step 6: Define the output
Step 7: Add the procedure to the database
Step 8: Call the procedure from Power BI
Performing sentiment analysis in Power BI using Python in SQL Server Machine Learning Services
Add pre-built Python models to SQL Server Machine Learning Services
Step 1: Check to see if the pre-trained models are installed
Step 2: Open PowerShell as administrator
Step 3: Download PowerShell script
Step 4: Run the downloaded script in PowerShell
Troubleshooting
Use pre-built Python sentiment model in SQL Server Machine Learning Services to score data in Power BI
Step 1: Begin defining the stored procedures
Step 2: Define variables
Step 3: Set @Query
Step 4: Set @PythonScript
Step 5: Configure sp_execute_external_script
Step 6: Define the output
Step 7: Add the procedure to the database
Step 8: Call the procedure from Power BI
Calculating distance in Power BI using R in SQL Server Machine Learning Services
Step 1: Make sure dplyr is loaded in SSMLS
Step 2: Launch SSMS and connect to a SQL Server
Step 3: Add the CalculateDistance database to the server
Step 4: Add the stored procedure that will calculate the distances
Step 5: Call the Power BI procedure from Power BI
Calculating distance in Power BI using Python in SQL Server Machine Learning Services
Step 1: Launch SSMS and connect to a SQL Server
Step 2: Add the CalculateDistance database to the server
Step 3: Add the stored procedure that will calculate the distances
Step 4: Call the Power BI procedure from Power BI
Index