SQLServerCentral Article

How to Get Data Using Python in Power BI



Get data from Python in Power BI is a possible. Python is one of the most popular programming languages. Several websites consider Python as the #1. Even the DBAs feel some pressure to learn Python. SQL Server included some features to integrate with Python and run python code. Power BI is not the exception. In this article, we will learn to get data from Python and send it to Power BI.


  1. First of all, I will use a Windows Machine with Power BI Desktop installed.
  2. Finally, internet access to download the installer.

Installing Python

First, you need to have Python installed. As we said before, Python is a pretty nice programming language. It includes a lot of useful libraries to handle data and to create advanced statistical graphics. That is why it is so popular and used in Power BI. The following link provides the Python installer: Python installer

Also, you need to have Pandas installed on your machine. This is a Python library used to manipulate and analyze data. To install Pandas, use the following command line.

pip install pandas

Enable Python in Power BI

In order to have Python enabled, in the Power BI Desktop, in the menu go to File>Options and settings and select options.

In addition, go to Python scripting and check if the home directory and the detected Python IDEs were detected.

Python in Power BI configuration settings

Getting Data from Python in Power BI

Once we have everything ready, press the Get data icon.

Get Data Power BI Get Data icon

Secondly, write pyt to look for the Python script and select it and press Connect.

Option to get Python in Power BI Python script option

Thirdly, write a script to get the data.

The script used is the following:

import pandas as p
data = [['Sylvester','Stallone',76],['Arnold','Sschwarzenegger',74]]
actors = p.DataFrame(data,columns=['Firstname','Lastname','Age'])

Script Explained for Python in Power BI

First, we need to import the pandas library. The import is basically saying that we are using the pandas library in our script. It is like the include used in C++ or other languages for libraries.

Secondly, we have the data with the Firstname, Lastname and Age columns. We have two actors. Sylvester Stallone who is 76 years old and Arnold who is 74.

Python in Power BI information

Once you press OK, you can select the data, Load, Transform Data, or Cancel. Load is to load directly without changes. Transform is used to modify column names, format or even you can add some columns using DAX. In this example, we will simply Load the data.

If everything is fine, you will be able to visualize it in Power BI.

Load Data from Python in Power BI using the read_csv Function

In addition, we have a Panda function to read data from a CSV file. In this example, we will retrieve information from a CSV file named Import_Sample_en.csv (download from resources if you want to use this file).

Also, we have the CSV file stored in the c drive and the data folder. The code used to import is the following.

users = pandas.read_csv("c:/data/Import_User_Sample_en.csv")

Finally, if everything is OK, you will be able to visualize the data.

If you have some experience with Power BI, you may say that Power BI can already import CSV files without Python, but if you have programming experience, you also know that you can combine a lot of code and logic combined with this function. Let’s add some logic to data in Python.

Conditional Data from Python in Power BI

The next example will list some numbers using a list of numbers and classify in other columns the odd and even numbers.

First, we will write the code for this.

import pandas as pd
data = {'list_of_numbers': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]}
numbers = pd.DataFrame(data)
numbers.loc[numbers['list_of_numbers'] % 2 == 0, 'Odd_Even'] = 'Even'
numbers.loc[numbers['list_of_numbers'] % 2 != 0, 'Odd_Even'] = 'Odd'

As you can see the data, we can see the odd and the even numbers.

Basically, we checked if the numbers can or cannot be divided into two equal parts using de % 2.

If the number cannot be divided into two equal parts, it is Odd. Otherwise, it is Even.

Generating random numbers with Python in Power BI

Finally, we will generate random numbers and load the values in Power BI. The code used is the following:

import random
import pandas as pd
res = [random.randrange(22, 33) for i in range(3)]
randlist = pd.DataFrame(res)

The result is 3 random numbers.

For this example, we are using pandas and random.

We are generating 3 random values and adding them to a list.


In this article, we show different examples of code in Python to import data. Also, we generated random values, we work with static data, we imported data from a CSV file. Also, we created some dynamic column values based on another column.

Python is a very popular programing language and there are thousands of tutorials on the web. If there is something that you cannot do with PowerShell, Python may save you if you know how to use it.



5 (1)




5 (1)