Python requests working in Power Query not when loaded in Data Model

[Anonymous]

My Python script is not successfully loading into the Data Model. The script works; it runs in an IDE (I use Pycharm), and also runs and returns data in the Power Query Editor. I am using it to get data from an API, using Python as it is iterative, uses parsed HTML to create the POST requests required, other reasons....

I have the following error (from the requests library):

Query1

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] ADO.NET: Python script error. <pi>requests.exceptions.MissingSchema: Invalid URL 'nan': No schema supplied. Perhaps you meant http://nan? </pi>. '.

The program flow is as follows:

Parameter query (url) -> Parameters table (#sections[Section1]) -> Python script

where I use a dynamic parameter as the url (there are multiple urls in production version), create the Parameters table using sections, and then pass the parameters to Python where I use requests to retrieve the data and import it back to Power BI.

The example scenario generating the error is:

url = "https://jsonplaceholder.typicode.com/todos/" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Parameters =

let
Source = Table.FromRecords({#sections[Section1]}),
#"Demoted Headers" = Table.DemoteHeaders(Source),
#"Unpivoted Columns" = Table.Transpose(#"Demoted Headers"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Column2", "Value"}, {"Column1", "Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Value"})
in
#"Removed Errors"

Query1 =

let
Source = Parameters,
#"Run Python script" = Python.Execute(python_script,[dataset=Source])
in
#"Run Python script"

where python_script = 

# 'dataset' holds the input data for this script

import requests
import pandas as pd
import json

url = dataset['Value'][0]

r = requests.get(url)

listi = []

for i in json.loads(r.content.decode('utf-8')):
listi.append(i['title'])

df = pd.DataFrame({"col1": listi})

As I said, this works up until I try to load into the model, then it returns an error. I don't know how Power BI actually processes Python so debugging is a struggle, hoping someone else has had and fixed this error before. Help appreciated!

[YukiK]

I'm not 100% sure on this, but I always had to add print(df) at the end of the script. Never seen your error, but maybe it's worth a try

[Anonymous]

Hey @YukiK, thanks I did try but unfortunately same outcome 😞 for extra clarity, this is the screenshot from inside the Editor:

but I receive the error I posted originally if I hit "Close & Apply".

On a side note, I think that Power BI pulls in all active variables that have type DataFrame - if you delete the variable using del command (e.g. del df) then Power BI won't import it back. Not sure where print actually prints to, happy to learn 🙂 Thanks anyway!

[Anonymous]

I am having a similar issue, but I am pulling data to a csv then trying to import them in to SQL table daily and almost same error.

import pyodbc # Some other example server values are # server = 'myserver.database.windows.net' # for a named instance # server = 'myserver,port' # to specify an alternate port server = 'tcp:myserver.database.windows.net' database = 'mydb' username = 'myusername' password = 'mypassword' cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = cnxn.cursor()

[Anonymous]

@Anonymous, sorry, what is your actual error message?