Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Can I run external Python scripts in Power BI desktop?

I'm really enjoying integrating Python scripts into my queries, but I'm starting to run into an issue. I want to use input parameters for my python scripts, and it seems the only way to do this also ...
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    This is a good suggestion - you may want to raise it as an idea.  

    The problem (as I see it) is the interpretative implementation chosen by the Power BI (Power Query) developers (basically Expression.Evaluate() ).  You can abuse the extra parameter in the Python call to hand over the dataset source but it's still clunky.  The same approach is present on the Power BI visual side, by the way, even more restrictive.

  • Anonymous's avatar
    Anonymous
    4 years ago

    So while this didn't directly answer my question, you gave me a few search terms that lead me to this page:

    https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/

    Where I was able to do a similar technique to import a local python file as a string var:

    let
    PythonFile = Text.FromBinary(File.Contents("C:\test.py")),
    Source = Python.Execute(PythonFile)
in
    Source

    Now, how can I put an input variable in the python script without breaking the string importing? Might have to split the python code into two files and build the imported python string in the M Code before running it as a complete block.

lbendlin's avatar
lbendlin
Super User
4 years ago

Yes, it's basically back to square one.  What you could do is create your own language convention that somehow identifies your variables in your python code, and then does some text manipulation to split your python string in the right places and slices in the variable names as expected by Power Query...

Anonymous's avatar
Anonymous
Not applicable
4 years ago

This actually turned out to be WAY easier than I expected!

So in my python script I tried to escape the string by naming it like:

input_var = "&PowerBIvar&"

But what did work very nicely was following that up with this in the M Code:

PythonFile = Text.FromBinary(File.Contents("C:\test.py")),
PythonString = Text.Replace(PythonFile,"&PowerBIvar&",PowerBIvar),
Output = Python.Execute(PythonString)