Forum Discussion

ferdilda's avatar
ferdilda
Frequent Visitor
10 months ago
Solved

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 informati...
Need Help
  • ferdilda's avatar
    ferdilda
    10 months ago

    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

ferdilda's avatar
ferdilda
Frequent Visitor
10 months ago

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's avatar
tayloramy
Super User
10 months ago

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's avatar
    ferdilda
    Frequent Visitor
    10 months ago

    That gave me this:

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

     

    Thank you for your help!

    • ferdilda's avatar
      ferdilda
      Frequent Visitor
      10 months ago

      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