Python Script in Power Query Function

[ferdilda]

I'm trying to make a power query function that loads its parameters into a python query.

Here is the function code:

It makes a proper function GUI:

But, when I input information and invoke the function, I get this:

Not sure what I'm doing wrong. How do I bring in the outside parameters from power query into Python?

Thanks!

[tayloramy]

Hi @ferdilda, 

Interesting, passing parameters through the execute function like that should be type agnostic, but it is good to hear that it got passed the actual table field. 

You can probably put the string variables back with the & concatenation and hopefully everything works. 

 If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.  

[tayloramy]

Hi @ferdilda, 

The problem here is that your dataset is of type table, and you're trying to concatinate that with a string in the Python code. 

You need to first convert the dataset parameter into a string, and then concatinate it. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.  

[ferdilda]

I don't want it to be type string, it's a table. I need that table to become a dataframe that I can use in the python function.

When I call it without concatenating

I get:

[tayloramy]

Hi @ferdilda, 

If that's the case, you will need to do a lot more advanced code to get this to work. 

The & operator is for concatination, but what you are wanting is to convert types through this.

Try this: 

let
    RunPythonWithParams =
    (dataset as table, divider_col as text, divider_options as text, data_type as text) =>
    let
        PythonCode =
"import sys, os
import pandas as pd

module_directory = os.path.abspath(r'C:\Users\larsal\OneDrive - Springs Window Fashions, LLC\Documents\Repo')
if module_directory not in sys.path:
    sys.path.append(module_directory)

from utils import anova_one

result = anova_one(dataset, divider_col, divider_options, data_type)
",
        Py = Python.Execute(
                PythonCode,
                [
                    dataset = dataset,
                    divider_col = divider_col,
                    divider_options = divider_options,
                    data_type = data_type
                ]
            ),
        Output = Py{[Name = "result"]}[Value]
    in
        Output
in
    RunPythonWithParams

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.  

[ferdilda]

That gave me this:

It's probably because divider_col, and the following variables are strings.

Thank you for your help!

[ferdilda · Accepted solution]

It was a long walk, but copilot and I figured it out:

let
     RunPythonWithParams =
     (dataset as table, divider_col as text, divider_options as text, data_type as text) =>
     let
          divider_col_table = #table({"Value"}, {{divider_col}}),
          divider_options_table = #table({"Value"}, {{divider_options}}),
          data_type_table = #table({"Value"}, {{data_type}}),

PythonCode = "
import sys, os
import pandas as pd

# Extract scalar values
divider_col = divider_col['Value'][0]
divider_options = divider_options['Value'][0]
data_type = data_type['Value'][0]

# Add your module path
module_directory = os.path.abspath(r'C:\\xxx')
if module_directory not in sys.path:
sys.path.append(module_directory)

# Import your function
from utils import anova_one

# Run the analysis
output = anova_one(dataset, divider_col, divider_options, data_type)

# Ensure the result is a DataFrame
     if isinstance(output, pd.DataFrame):
     result = output
else:
     result = pd.DataFrame({'Error': ['anova_one did not return a DataFrame']})

# Return named output
{'result': result}
",

          Py = Python.Execute(
               PythonCode,
               [
                    dataset = dataset,
                    divider_col = divider_col_table,
                    divider_options = divider_options_table,
                    data_type = data_type_table
               ]
          ),
          Output = Py{[Name = "result"]}[Value]
     in
          Output
in
     RunPythonWithParams