March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
Do you have a gateway installed and is Python installed on the gateway?
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-python-in-query-editor
https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-personal-mode
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.
Do you have a gateway installed and is Python installed on the gateway?
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?
I did not, had not realized that was necessary, but I now installed it and it worked!
how did you do that. Thanks in advance
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
61 | |
53 | |
26 | |
17 | |
13 |