Microsoft Power BI and Python: Two Superpowers Combined

Install Microsoft Power BI Desktop

Microsoft Power BI is a collection of various tools and services, some of which require a Microsoft account, a subscription plan, and an Internet connection. Fortunately for you, in this tutorial, you’ll use Microsoft Power BI Desktop, which is completely free of charge, doesn’t require a Microsoft account, and can work offline just like a traditional office suite.

There are a few ways in which you can obtain and install Microsoft Power BI Desktop on your computer. The recommended approach, which is arguably the most convenient one, is to use Microsoft Store, accessible from the Start menu or its web-based storefront:

By installing Power BI Desktop from the Microsoft Store, you’ll ensure automatic and quick updates to the latest Power BI versions without having to be logged in as the system’s administrator. However, if that method doesn’t work for you, then you can always try downloading the installer from the Microsoft Download Center and running it manually. The executable file is roughly four hundred megabytes in size.

Once you have the Power BI Desktop application installed, launch it, and you’ll be greeted with a welcome screen similar to the following one:

Don’t worry if the Power BI Desktop user interface feels intimidating at first. You’ll get to know the basics as you make your way through the tutorial.

Install Microsoft Visual Studio Code

Microsoft Power BI Desktop offers only rudimentary code editing features, which is understandable since it’s mainly a data analysis tool. It doesn’t have intelligent contextual suggestions, auto-completion, or syntax highlighting for Python, all of which are invaluable when working with code. Therefore, you should really use an external code editor for writing anything but the most straightforward Python scripts in Power BI.

Feel free to skip this step if you already use an IDE like PyCharm or if you don’t need any of the fancy code editing features in your workflow. Otherwise, consider installing Visual Studio Code, which is a free, modern, and extremely popular code editor. Because it’s made by Microsoft, you can quickly find it in the Microsoft Store:

Microsoft Visual Studio Code, or VS Code as some like to call it, is a universal code editor that supports many programming languages through extensions. It doesn’t understand Python out of the box. But when you open an existing file with Python source code or create a new file and select Python as the language in VS Code, then it’ll prompt you to install the recommended set of extensions for Python:

After you confirm and proceed, VS Code will ask you to specify the path to your Python interpreter. In most cases, it’ll be able to detect one for you automatically. If you haven’t installed Python on your computer yet, then check out the next section, where you’ll also get your hands on pandas and Matplotlib.

Install Python, pandas, and Matplotlib

Now it’s time to install Python, along with a couple of libraries required by Power BI Desktop to make your Python scripts work in this data analysis tool.

If you’re a data analyst, then you may already be using Anaconda, a popular Python distribution that bundles hundreds of scientific libraries and a custom package manager. Data analysts tend to choose Anaconda over the standard Python distribution because it makes their environment setup more convenient. Ironically, setting up Anaconda with Power BI Desktop is more cumbersome than using standard Python, and it’s not even recommended by Microsoft:

Distributions that require an extra step to prepare the environment (for example, Conda) might encounter an issue where their execution fails. We recommend using the official Python distribution from https://www.python.org/ to avoid related issues. (Source)

Anaconda usually uses the release of Python a few generations back, which is another reason to prefer the standard distribution if you want to stay on the cutting edge. That said, you’ll find some help on how to use Anaconda and Power BI Desktop in the next section.

If you’re starting from scratch without having installed Python on your computer before, then your best option is to use Microsoft Store again. Find the most recent Python release and proceed with installing it:

When the installation is complete, you’ll see a couple of new entries in your Start menu. It’ll also make the python command immediately available to you in the command prompt, along with pip for installing third-party Python packages.

Power BI Desktop requires your Python installation to have two extra libraries, pandas and Matplotlib, which aren’t provided as standard unless you’ve used Anaconda. However, installing third-party packages into the global or system-wide Python interpreter is considered a bad practice. Besides, you wouldn’t be able to run the system interpreter from Power BI due to permission restrictions on Windows. You need a Python virtual environment instead.

A virtual environment is a folder that contains a copy of the global Python interpreter, which you’re free to mess around with. You can install extra libraries into it without worrying about breaking other programs that might also depend on Python. At any point, you can safely remove the folder containing your virtual environment and still have Python on your computer afterward.

PowerShell forbids running scripts by default, including those for managing Python virtual environments, because of a restricted execution policy that’s in place. Before you can activate your virtual environment, there’s some initial setup required. Go to your Start menu, find Windows Terminal, right-click on it, choose Run as administrator, and confirm by clicking Yes. Next, type the following command to elevate your execution policy:

PS> Set-ExecutionPolicy RemoteSigned

Make sure you’re running this command as the system administrator in case of any errors.

The RemoteSigned policy will allow you to run local scripts as well as scripts downloaded from the Internet as long as they’re signed by a trusted authority. This configuration needs to be done only once, so you can close the window now. However, there’s much more to setting up a Python coding environment on Windows, so feel free to check out the guide if you’re interested.

Now, it’s time to pick a parent folder for your virtual environment. It could be in your workspace for Power BI reports, for example. However, if you’re unsure where to put it, then you can use your Windows user’s Desktop folder, which is quick to locate. In such a case, right-click anywhere on the desktop and choose the Open in Terminal option. This will open the Windows Terminal with your desktop as the current working directory.

Next, use Python’s venv module to create a new virtual environment in a local folder. You can name the folder powerbi-python to remind yourself of its purpose later:

PS> python -m venv powerbi-python

After a few seconds, there will be a new folder with a copy of the Python interpreter on your desktop. Now, you can activate the virtual environment by running its activation script and then install the two libraries expected by Power BI. Type the following two commands while the desktop is still your current working directory:

PS> .\powerbi-python\Scripts\activate
(powerbi-python) PS> python -m pip install pandas matplotlib

After activating it, you should see your virtual environment’s name, powerbi-python, in the prompt. Otherwise, you’d be installing third-party packages into the global Python interpreter, which is what you wanted to avoid in the first place.

All right, you’re almost set. You can repeat the two steps of activating your virtual environment and using pip to install other third-party packages if you feel like adding more libraries to your environment. Next up, you’ll tell Power BI where to find Python in your virtual environment.

Configure Power BI Desktop for Python

Return to Power BI Desktop or, if you’ve already closed it, start it again. Dismiss the welcome screen by clicking the X icon in the top-right corner of the window, and select Options and Settings from the File menu. Then, go to Options, which has a gear icon next to it:

This will reveal a number of configuration options grouped by categories. Click the group labeled Python scripting in the column on the left, and set the Python home directory by clicking the Browse button depicted below:

You must specify the path to the Scripts subfolder, which contains the python.exe executable, in your virtual environment. If you put the virtual environment in your Desktop folder, then your path should look something like this:

C:\Users\User\Desktop\powerbi-python\Scripts

Replace User with whatever your username is. If the specified path is invalid and doesn’t contain a virtual environment, then you’ll get a suitable error message.

(base) C:\Users\User> conda create --name powerbi-python pandas matplotlib

(base) C:\Users\User> conda env list
# conda environments:
#
base                  *  C:\Users\User\anaconda3
powerbi-python           C:\Users\User\anaconda3\envs\powerbi-python

While still in the Python scripting options, you’ll find another interesting configuration down below. It lets you specify the default Python IDE or code editor that Power BI should launch for you when you’re writing a code snippet. You can keep the operating system’s default program associated with the .py file extension or you can indicate a specific Python IDE of your choice:

To specify your favorite Python IDE, select Other from the first dropdown and browse to the executable file of your code editor, such as this one:

C:\Users\User\AppData\Local\Programs\Microsoft VS Code\Code.exe

As before, the correct path on your computer might be different. For more details on using an external Python IDE with Power BI, check out the online documentation.

Congratulations! That concludes the configuration of Power BI Desktop for Python. The most important setting is the path to your Python virtual environment, which should contain the pandas and Matplotlib libraries. In the next section, you’ll see Power BI and Python in action.

Data Source: Import a pandas.DataFrame

Suppose you must ingest data from a proprietary or a legacy system that Power BI doesn’t support. Maybe your data is stored in an obsolete or not-so-popular file format. In any case, you can whip up a Python script that’ll glue the two parties together through one or more pandas.DataFrame objects.

A DataFrame is a tabular data storage format, much like a spreadsheet or a table in a relational database. It’s two-dimensional and consists of rows and columns, where each column typically has an associated data type, such as a number or date. Power BI can grab data from Python variables holding pandas DataFrames, and it can inject variables with DataFrames back into your script.

In this tutorial, you’ll use Python to load fake sales data from SQLite, which is a widespread file-based database engine. Note that it’s technically possible to get such data directly in Power BI Desktop, but only after installing a suitable SQLite driver and using the ODBC connector. On the other hand, Python supports SQLite right out of the box, so choosing it may be more convenient.

Before jumping into the code, it would help to explore your dataset to get a feel for what you’ll be dealing with. It’s going to be a single table consisting of used car dealership data stored in the car_sales.db file. Remember that you can download this sample dataset by clicking the link below:

There are a thousand records and eleven columns in the table, which represent sold cars, their buyers, and the corresponding sales details. You can quickly visualize this sample database by loading it into a pandas DataFrame and sampling a few records in a Jupyter Notebook using the following code snippet:

import sqlite3
import pandas as pd

with sqlite3.connect(r"C:\Users\User\Desktop\car_sales.db") as connection:
    df = pd.read_sql_query("SELECT * FROM sales", connection)

df.sample(15)

Note that the path to the car_sales.db file may be different on your computer. If you can’t use Jupyter Notebook, then try installing a tool like SQLite Browser and loading the file into it. Either way, the sample data should be represented as a table similar to the one below:

At a glance, you can tell that the table needs some cleaning because of several problems with the underlying data. However, you’ll deal with most of them later, in the Power Query editor, during the data transformation phase. Right now, focus on loading the data into Power BI.

As long as you haven’t dismissed the welcome screen in Power BI yet, then you’ll be able to click the link labeled Get data with a cylinder icon on the left. Alternatively, you can click Get data from another source on the main view of your report, as none of the few shortcut icons include Python. Finally, if that doesn’t help, then use the menu at the top by selecting Home › Get data › More… as depicted below:

Doing so will reveal a pop-up window with a selection of Power BI connectors for several data sources, including a Python script, which you can find by typing python into the search box:

Select it and click the Connect button at the bottom to confirm. Afterward, you’ll see a blank editor window for your Python script, where you can type a brief code snippet to load records into a pandas DataFrame:

Notice the lack of syntax highlighting or intelligent code suggestions in the editor built into Power BI. As you learned earlier, it’s much better to use an external code editor, such as VS Code, to test that everything works as expected and only then paste your Python code to Power BI.

Before moving forward, you can double-check if Power BI uses the right virtual environment, with pandas and Matplotlib installed, by reading the text just below the editor.

While there’s only one table in the attached SQLite database, it’s currently kept in a denormalized form, making the associated data redundant and susceptible to all kinds of anomalies. Extracting separate entities, such as cars, sales, and customers, into individual DataFrames would be a good first step in the right direction to rectify the situation.

Fortunately, your Python script may produce as many DataFrames as you like, and Power BI will let you choose which ones to include in the final report. For example, you can extract those three entities with pandas using column subsetting in the following way:

import sqlite3
import pandas as pd

with sqlite3.connect(r"C:\Users\User\Desktop\car_sales.db") as connection:
    df = pd.read_sql_query("SELECT * FROM sales", connection)

cars = df[
    [
        "vin",
        "car",
        "mileage",
        "license",
        "color",
        "purchase_date",
        "purchase_price",
        "investment",
    ]
]
customers = df[["vin", "customer"]]
sales = df[["vin", "sale_price", "sale_date"]]

First, you connect to the SQLite database by specifying a suitable path for the car_sales.db file, which may look different on your computer. Next, you run a SQL query that selects all the rows in the sales table and puts them into a new pandas DataFrame called df. Finally, you create three additional DataFrames by cherry-picking specific columns. The vehicle identification number (VIN) works as a primary key by tying related records.

When you click OK and wait for a few seconds, Power BI will present you with a visual representation of the four DataFrames produced by your Python script. Assuming there were no syntax errors and you specified the correct file path to the database, you should see the following window:

The resulting table names correspond to your Python variables. When you click on one, you’ll see a quick preview of the contained data. The screenshot above shows the customers table, which comprises only two columns.

Select cars, customers, and sales in the hierarchical tree on the left while leaving off df, as you won’t need that one. You could finish the data import now by loading the selected DataFrames into your report. However, you’ll want to click a button labeled Transform Data to perform data cleaning using pandas in Power BI.

In the next section, you’ll learn how to use Python to clean, transform, and augment the data that you’ve been working with in Power BI.

Power Query Editor: Transform and Augment Data

If you’ve followed the steps in this tutorial, then you should’ve ended up in the Power Query Editor, which shows the three DataFrames that you selected before. They’re called queries in this view. But if you’ve already loaded data into your Power BI report without applying any transformations, then don’t worry! You can bring up the same editor anytime.

Navigate to the Data perspective by clicking the table icon in the middle of the ribbon on the left and then choose Transform data from the Home menu:

Alternatively, you can right-click one of the Fields in the Data view on the far right of the window and choose Edit query for the same effect. Once the Power Query Editor window appears again, it’ll contain your DataFrames or Queries on the left and the Applied Steps on the right for the currently selected DataFrame, with rows and columns in the middle:

Steps represent a sequence of data transformations applied top to bottom in a pipeline-like fashion against a query. Each step is expressed as a Power Query M formula. The first step, named Source, was the invocation of your Python script that produced four DataFrames based on the SQLite database. The other two steps fish out the relevant DataFrame and then transform the column types.

You can insert custom steps into the pipeline for more granular control over data transformations. Power BI Desktop offers plenty of built-in transformations that you’ll find in the top menu of Power Query Editor. But in this tutorial, you’ll explore the Run Python script transformation, which is the second mode of running Python code in Power BI:

Conceptually, it works almost identically to data ingestion, but there are a few differences. First of all, you may use this transformation with any data source that Power BI supports natively, so it could be the only use of Python in your report. Secondly, you get an implicit global variable called dataset in your script, which holds the current state of the data in the pipeline, represented as a pandas DataFrame.

Pandas lets you extract values from an existing column into new columns using regular expressions. For example, some customers in your table have an email address enclosed in angle brackets (<>) next to their name, which should really belong to a separate column.

Select the customers query, then select the last Changed Type step, and add a Run Python script transformation to the applied steps. When the pop-up window appears, type the following code fragment into it:

# 'dataset' holds the input data for this script
dataset = dataset.assign(
    full_name=dataset["customer"].str.extract(r"([^<]+)"),
    email=dataset["customer"].str.extract(r"<([^>]+)>")
).drop(columns=["customer"])

Power BI injects the implicit dataset variable into your script to reference the customers DataFrame, so you access its methods and override it with your transformed data. Alternatively, you could define a new variable for the resulting DataFrame. During the transformation, you assign two new columns, full_name and email, and then remove the original customer column that contained both pieces of information.

After clicking OK and waiting for a few seconds, you’ll see a table with the DataFrames your script produced:

There’s only one DataFrame, called dataset, because you reused the implicit global variable provided by Power BI for your new DataFrame. Go ahead and click the yellow Table link in the Value column to choose your DataFrame. This will generate a new output of your applied steps:

Suddenly, there are two new columns in your customers table. Now you can instantly find customers who haven’t provided their email addresses. If you want to, you can add more transformation steps to, for example, split the full_name column into first_name and last_name, assuming there are no edge cases with more than two names.

Make sure that the last step is selected, and insert yet another Run Python script into the applied steps. The corresponding Python code should look as follows:

# 'dataset' holds the input data for this script
dataset[
    ["first_name", "last_name"]
] = dataset["full_name"].str.split(n=1, expand=True)
dataset.drop(columns=["full_name"], inplace=True)

Unlike in the previous step, the dataset variable refers to a DataFrame with three columns, vin, full_name, and email, because you’re further down the pipeline. Also, notice the inplace=True parameter, which drops the full_name column from the existing DataFrame rather than returning a new object.

You’ll notice that Power BI gives generic names to the applied steps and appends consecutive numbers to them in case of many instances of the same step. Fortunately, you can give the steps more descriptive names by right-clicking on a step and choosing Rename from the context menu:

By editing Properties…, you may also describe in a few sentences what the given step is trying to accomplish.

There’s so much more you can do with pandas and Python in Power BI to transform your datasets! For example, you could:

• Anonymize sensitive personal information, such as credit card numbers
• Identify and extract new entities from your datasets
• Reject car sales with missing transaction details
• Remove duplicate sales records
• Synthesize the model year of a car based on its VIN
• Unify inconsistent purchase and sale date formats

These are just a few ideas. While there’s not enough room in this tutorial to cover everything, you’re more than welcome to experiment on your own and check out the bonus materials. Note that your success with using Python to transform data in Power BI will depend on your knowledge of pandas, which Power BI uses under the hood. To brush up on your skills, check out the pandas for Data Science learning path here at Real Python.

When you’re finished transforming your datasets, you can close the Power Query Editor by choosing Close & Apply from the Home ribbon or its alias in the File menu:

This will apply all transformation steps across your datasets and return to the main window of Power BI Desktop.

Next up, you’ll learn how to use Python to produce custom data visualizations.

Visuals: Plot a Static Image of the Data

So far, you’ve imported and transformed data. The third and final application of Python in Power BI Desktop is plotting the visual representation of your data. When creating visualizations, you can use any of the supported Python libraries as long as you’ve installed them in the virtual environment that Power BI uses. However, Matplotlib is the foundation for plotting, which those libraries delegate to anyway.

Unless Power BI has already taken you to the Report perspective after transforming your datasets, navigate there now by clicking the chart icon on the left ribbon. You should see a blank report canvas where you’ll be placing your graphs and other interactive components, jointly named visuals:

Over on the right in the Visualizations palette, you’ll see a number of icons corresponding to the available visuals. Find the icon of the Python visual and click it to add the visual to the report canvas. The first time you add a Python or R visual to a Power BI report, it’ll ask you to enable script visuals:

In fact, it’ll keep asking you the same question in each Power BI session because there’s no global setting for this. When you open a file with your saved report that uses script visuals, you’ll have the option to review the embedded Python code before enabling it. Why? The short answer is that Power BI cares for your privacy, as any script could leak or damage your data if it’s from an untrusted source.

Expand your cars table in the Fields toolbar on the right and drag-and-drop its color and vin columns onto the Values of your visual:

These will become the only columns of the implicit dataset DataFrame provided by Power BI in your Python script. Adding those data fields to a visual’s values enables the Python script editor at the bottom of the window. Somewhat surprisingly, this one does offer basic syntax highlighting:

However, if you’ve configured Power BI to use an external code editor, then clicking on the little skewed arrow icon (↗) will launch it and open the entire scaffolding of the script. You can ignore its content for the moment, as you’ll explore it in an