Forum Discussion
Python Script in Power Query Function
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!
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
6 Replies
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.
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:
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 RunPythonWithParamsIf you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
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.
