Power BI pass parameter value to python script

Power BI pass parameter value to python script

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 add 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.

power bi add new column with python

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.

power bi parameter

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

parameters in power bi

And this is how my new column looks like, this 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 pass parameter value to python script

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

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

Power BI pass parameter value to python script
Power BI pass parameter to 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.

visualize parameter value in power bi
  • Click on OK
  • Apply changes
  • Script Execution: “Run”

And here we are:

display parametere value in power bi

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.

Power BI pass parameter to python script

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.

dynamic parameter power bi

Pass text parameter to python script

pass parameter value to python in power bi

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 as 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/

One thought on “Power BI pass parameter value to python script

Leave a Reply

Your email address will not be published. Required fields are marked *