Can I run external Python scripts in Power BI desktop?

[gzimmer]

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 breaks the built in Python editor and makes reading the M Code very ugly. 

    Source = Python.Execute("from urllib.parse import unquote#(lf)import re#(lf)import io#(lf)#(lf)input_url = """&SafeLinkURL&"""#(lf)table = """"""url;#(lf)""""""+input_url+""""""#(lf)""""""#(lf)df = pandas.read_csv(io.StringIO(table))#(lf)print(df)#(lf)"),

 It seems to me that a good solution would be to externalize the python script better...
Is there a way to run local external python scripts via the Python.Execute function that can be easily maintained?

[lbendlin · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[gzimmer · Accepted solution]

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]

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

[gzimmer]

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)