top of page

Learning To Use Python in Power BI

I’m pretty familiar with Power BI, I work with it every day, I write this blog, and I keep on top of video and subreddit discussions like a hawk, but there’s always been one set of features that I’ve always felt were way beyond me. Kind of like being in a house for years but never looking in that dark spooky corner because it may turn out you don’t know your house as well as you thought you do.

​

Well for me the dark scary corner of Power BI has been the Python functionality, (and R to an extent too). So as with most things in my life that scare and confuse me I decide to jump in head first. While I have experience across DAX, M, SQL, JSON, and HTML/CSS, it’s really only a light working knowledge of proper fully fledged programming languages, so this is completely new territory.

​

Really, this was an experiment and so I’m going to write it almost as a log of each step. My logic is that experiencing this as a beginner will be helpful for others in a similar position.  The overall structure is going to be:

​

  • My Experiment with Python

  • Limitations & Considerations

  • Conclusion

  • Resources and next steps

​​

I don’t have a particular methodology here but as I’m starting to get used to using ChatGPT as an extremely advanced virtual assistant I’ve started to feel comfortable integrating it into my learning and as such use it quite heavily. Obviously ChatGPT can help from a technical perspective, it (and similar language models like Bard) is a tool with a seemingly infinite use of applications when it comes to knowledge/language-based work but it seems important to think for a second about whether using a tool like that will actually help you learn rather than just spit code out. The conclusion was that it’s up to you as the user to use it in a way which enhances what you’re doing rather than replace. As good as it is though if you read on, you’ll see that ChatGPT and I eventually get to an impasse where either I, it, or both of us just didn’t have the resources or faculties to figure out the next step in the time available.

​

Step by Step – Learning to Use Python in Power BI

​

Step 1 – Research

With any new topic I always pencil in the first step as research. How much research happens depends on what it is, some areas like this need just enough to get you started, after which learn by doing is invariably the most effective method.

​

As always, the Guy In A Cube YouTube channel was my first stop and I gave this video a watch https://youtu.be/3_DOF_qjguA Essentially, Patrick goes through getting some data using a python script and then making a visual with the Python visual in Power BI.

​

I always find a lot of value in Reddit so I check google for any threads specifically about how people have made use of it and what their experiences had been, fortunately there are a few to read through. This is where I learned that Power BI is essentially the one dead end in uses for Python, not because it doesn’t work, but because it’s essentially only useful for some extremely niche cases. I do think about putting this experiment to an end at this point but as it’s essentially a perfect gateway for me into learning a bit of Python it seemed like a good thing to push forwards with.

​

What I’ve found so far is essentially that Python has three main uses in Power BI and each of them come with their own limitations:

  1. Connecting to data – By using a Python script and the Python script connector

  2. Transforming the data – By using the Python function in Power Query

  3. Visualising the data

Ultimately, all the above are better to do somewhere else. Also, two larger limitations stood out as well:

  1. It’s very slow compared to the native features.

  2. If you want to use the scheduled refresh in the service you need to use the less secure personal gateway rather than the enterprise gateways. As one user on reddit says: “Why would they make a feature that fails once you get to the deployment stage for an enterprise setup but works for personal gateways? Makes no sense at all”.

But between this and the video the goal of the experiment becomes clear: Create a visualisation using Python.

Step 2 – Install Python & Get Data

For this first part I was working alongside the Guy in A Cube video, essentially replicating what he was doing. In order to execute Python script it needs installing so that’s quickly sorted out by heading over to https://www.python.org/downloads/ and downloading the executable. The installation goes well. After that you’ll need to go into the options menu in Power BI and make sure using Python is enabled.

According to the video that’s all we need to get started. I use a website to understand how to put a quick table together and run it:

​

bottom of page