4 Solving Common Issues When Using Python and R in Power BI

Join our book community on Discord

https://packt.link/EarlyAccessCommunity

Sometimes you may run into some problems that are quite common for those developing solutions in Power BI using Python and R. This chapter systematically addresses those most common problem cases by providing the most appropriate solutions to each of them.This chapter will cover the following topics:

• Avoiding the ADO.NET error when running a Python script in Power BI
• Avoiding the Formula.Firewall error
• Using multiple datasets in one Python and R script step
• Dealing with dates/times in Python and R script steps

Technical Requirements

This chapter requires you to have a working internet connection and Power BI Desktop already installed on your machine (we used the version 2.112.603.0 64-bit, December 2022).

Avoiding the ADO.NET error when running a Python script in Power BI

We assume you have followed the steps in Chapter 3, Configuring Python with...

Using multiple datasets in Python and R script steps

You may have noticed how each query in Power Query has its own queue of transformation steps, leading from the initial data to the final dataset in the desired form. You may need to add a Python or R script step that uses a function to which you need to pass two dataframes as parameters to a query.Assuming I have the two queries query_A and query_B, which return the two datasets to be used as parameters for the above function, how do I reference the result of query_B in my script if I’m adding the script step to query_A?There are several ways to do this. Let's see them.

Applying a full join with Merge

The first trick that comes to mind for any analyst who is used to dealing with data is to apply a full join between the two datasets and thus generate a third dataset on which to apply the script step. Within the script step, the reverse merge transformation is applied, that is, separating the columns of the two original datasets...

Dealing with dates/times in Python and R script steps

The handling of date, date/time and time fields is native in Power BI. If you want to add a Python or R script step from a dataset containing a date/time field, you would expect to find that field in the default dataset dataframe with the corresponding data type depending on whether it is a Python or R script. Unfortunately, it is not so straightforward. But with a little forethought, it is possible to handle these data types in the right way.The code used in both Python and R makes use of date-specific functions with which you may not be too familiar. It is not important to completely know them for the purposes of understanding the section; you can delve into them later when you need to.In the folder pertaining to Chapter 4 in the GitHub repository associated with the book you will find the date-time-fields.xlsx Excel file containing the dates sheet with only date, date/time, and time fields:

Figure 4.49 – Content of the dates...

Summary

In this chapter, you covered some of the types of issues that are common when integrating Python or R scripts into Power BI.In particular, you learned how to avoid the indecipherable ADO.NET error that can occur when you use Power BI with a Conda environment that is not properly enabled.You learned about the different levels of privacy that are managed in Power BI and how to resolve incompatibility issues between them that are caused by the Formula Firewall.You also learned new techniques for referencing more than one dataset in a Python or R script, even though the step stack in which the script is inserted references only one dataset.Finally, you learned how to properly handle data types for date and time fields in step scripts in Python and R.In the next chapter, you'll start working with Python and R scripts in Power BI to perform data ingestion and import data sources that Power BI doesn't handle by default.

Test your knowledge

Q01. Why does Power BI Desktop sometimes raise the ADO.NET error referring to the inability to import the NumPy library, even though the package is correctly installed in the Conda environment referenced in the Options?Q02. What’s the goal of the Data Privacy Firewall, aka the Firewall?Q03. List the two types of errors that the Firewall can raise and describe their characteristics.Q04. Suppose you need an On-Premises Data Gateway in enterprise mode to allow some datasets to be refreshed after publishing your report to Power BI service. Will the published datasets have privacy levels aligned with those set on Power BI Desktop?Q05. Suppose you have a query that directly accesses a data source and involves a Python script step. The query has a privacy level set to Organizational, whereas the Python script has the privacy level set to Public. Does the firewall raise errors? If yes, which ones?Q06. What are the pros and cons of the two methods of resolving the indirect...

References

For additional reading, check out the following books and articles:

• An Introduction to R by R Core (https://cran.r-project.org/doc/manuals/r-release/R-intro.html)
• R for Data Science by Hadley Wickham (https://r4ds.had.co.nz/index.html)
• Machine Learning with R, 4^th Edition by Brett Lantz, Packt Publishing ()
• ggplot2: Elegant Graphics for Data Analysis (https://ggplot2-book.org/)
• Python for Data Analysis (https://wesmckinney.com/book/)
• Matplotlib User Guide (https://matplotlib.org/stable/users/index)

Test your knowledge

1. What could be the reason why you may want to import serialized files into R (.rds) or Python (.pkl)?
2. Is there a specific format of an R object that needs to be serialized so that it can then be deserialized in Power BI?
3. Why use an alternative method to inject a serialized object from a Python or R script step in Power Query into a Python or R script visual, when it is possible to deserialize the object directly in the visual?
4. Can you briefly summarize the alternative method for injecting a serialized object from Power Query into a script visual?
5. Why is it important to provide a relationship between the object name table (used in the slicer) and the table containing the byte string representation of objects and their names?

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

...