Please note javascript is required for full website functionality.


Power Query: Python Set

16 October 2019

Welcome to our Power Query blog. Today, I look at how to set up and use Python in Power BI.

Last time, I installed and set up Python ready for Power BI to use. Naturally, I’d also like to be able to use Python in ‘Get & Transform’ from Excel, but for now this is not yet available, so I’ll look at how to use Python in Power BI.

In the ‘Options’ tab, I found the Python sub screen:

I set up the IDE to point to the location of my Visual Studio Code.

I am ready to use Python as my source. Back on the ‘Home’ tab, I look at the options available from the ‘Get Data’ tab.

I can limit the amount of data options available by selecting ‘Other’.

I have the option ‘Python Script’, and hovering over this gives me more information ‘Run a Python script on a local Python installation to import data frames’. I choose this option.

I can enter a Python script here – I will use the same standard Microsoft example that I used last time.

import pandas as pd

data = [['Alex',10],['Bob',12],['Clarke',13]]

df = pd.DataFrame(data,columns=['Name','Age'],dtype=float)

print (df)

I click ‘OK’ to run the script. There is a slight pause whilst the connections are secured and the script is run, and then the ‘Navigator Pane’ appears.

I can select the table icon to see what data has been imported.

The data appears as expected. I choose to ‘Transform Data’.

My data is in the Power Query editor ready to be transformed. Once I am happy, I use ‘Close & Apply’ to make my data available.

I can now go back to my Power BI Desktop report canvas and select this data.

Next time, I will look at how to use Python to filter some of the data provided by my imaginary salespeople.

Come back next time for more ways to use Power Query!