Query contains unknown function name: Python.Execute

[Laila92]

I used a Python script in Power Query to pull data from a Google Sheet. It works fine in PBI Desktop, but I get an error for the automated refresh:

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

Discover Data Sources
Query contains unknown function name: Python.Execute Unknown function name: Python.Execute is a valid Mashup library function name format. Hence fail the operation.

 
This is the Python script that works on desktop but not for sched. refresh (anonimized the spreadsheet key):

import numpy as np
import pandas as pd
pd.options.mode.chained_assignment = None
import re
import gspread
import csv
from oauth2client.service_account import ServiceAccountCredentials
from df2gspread import df2gspread as d2g
import glob
from datetime import datetime
from datetime import date
import calendar
import requests
from dateutil.parser import parse




def connect_to_gsheets():
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(r'C:\Users\Laila\Documents\Python Scripts\google_spreadsheet_secret_key.json', scope)
    gc = gspread.authorize(credentials)
    return gc,credentials

def read_cost_sheet():
    gc,credentials = connect_to_gsheets()
    spreadsheet_key = 'xxxx'
    spreadsheet = gc.open_by_key(spreadsheet_key)
    worksheet = spreadsheet.worksheet("DATA")
    list_of_lists = worksheet.get_all_values()
    data=pd.DataFrame(list_of_lists)
    data.columns = data.iloc[0]
    return data
cost_data=read_cost_sheet()
print(cost_data)

 
I can't find what part of the script throws the error.

[JohnnyK]

Use Python in Power Query Editor (it mentions the use of Gateway)

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-in-query-editor

Use personal gateways in Power BI (it teaches how to install Gateway)

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personal-mode

[Anonymous]

Adding this just in case the first solution doesn't work for other people:

- Something that works for me is in the PBI file change the privacy level for Python to "Private" and republish.

[Greg_Deckler · Accepted solution]

Do you have a gateway installed and is Python installed on the gateway?

[rameshmadhubhas]

Hi @Greg_Deckler,
I got the same type of Issue:

How can I sort this issue without using an On-premises data gateway?
Can I use the VNet data gateway to address this issue?

[Laila92]

I did not, had not realized that was necessary, but I now installed it and it worked!

[purpleme_fin]

how did you do that. Thanks in advance

[Anonymous]

How can I install Python on the gateway?Thanks!

[Greg_Deckler]

Great! 🙂