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)
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!