In Power BI Desktop with Query Editor we can perform advanced custom transformation using Python or R script. In this short post, I will demonstrate how we can in Power BI pass parameter value to Python script.

Add a new column to a dataset with Python script

This basic line of code adds a new column “new_Column” to my dataset with the value “2” for every row.

dataset["new_Column"]=2

As we can observe from the data viewer our new column has been created with the value “2” for every single row.

Now let’s see how to pass a parameter in the Python script to create a new column based on a parameter.

Reference Power BI Parameter from Python Script

First, we need to create a parameter in Power BI.

I created “param1” which is a list of 10 digits ranging from 1 to 10.
Now I tried to reference the Power BI parameter using the following syntax ” & param1 & “.

And this is what my new column looks like, this is not really what we want so how can we make sure that Python reads my parameter?
The answer is we can’t! And I’m also asking the wrong question…

Power Query executes Python scripts using the function “python.execute()“. Now the question I should ask is simply how can we pass a parameter value into the power query function python.execute()?

Now the answer becomes quite simple; this function is expecting a text as an input (the Python script), and we already know how to add parameter value into a text with power query.

Power BI passes parameter value to Python script

All we need is to use ampersand “&” and double-quotes for the Text variable and the function “Text.From(value)” for variables that are not of type “text”.

Since the param1 type is a number I use the function Text.From() to pass this parameter to the Python script.

As we can see from the image above Power query recognized the parameter param1, now we can run the Python script and dynamically change the value assigned to the new column.

• Click on OK
• Apply changes
• Script Execution: “Run”

And here we are:

Pass multiple parameters to Python script

In the above example I’ve shown how to execute a Python script from PowerBI with one variable, now let’s pass two variables.
Let’s say that we want to add a new column based on the product of two parameters.

Just like the first example I use “&Text.From()&” to pass my two non-text variables to the Python script. This is pretty easy to pass as many variables as we want.

Pass text parameter to Python script

For text parameters, it is even simpler as we only need to wrap the parameter value with double-quotes like on the image above and then assign it like this in the Python script “& param4 &”.

To recap

• Passing parameters to Python or R script is fairly simple
• Once edited the Python script is transformed into an M script and it becomes difficult to edit.
• Does not work in Direct Query

To create visualizations using Python in Power BI you can refer to this post:
CREATE DYNAMIC POWER BI VISUALS BY USING PYTHON IN 1 MINUTE

And this post for R:
https://datakuity.com/2020/12/03/r-power-bi-add-target-marker-to-each-column-in-a-grouped-bar-chart/