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. requests.exceptions.MissingSchema: Invalid URL 'nan': No schema supplied. Perhaps you meant http://nan? . '.

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!

[v-zhangti]

Hi, @Anonymous 

You can launch your external Python IDE from Power BI Desktop and have your data automatically imported and displayed in the Python IDE. From there, you can modify the script in that external Python IDE, then paste it back into Power BI Desktop to create Power BI visuals and reports.

Please refer to the following documentation on using Python in Power BI to see if it helps you.

Use an external Python IDE with Power BI - Power BI | Microsoft Docs

Using Python in Power BI Power Query Editor - Power BI | Microsoft Docs

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Charlotte @v-zhangti ,

Sorry if there is a misunderstanding, editing the Python script is not an issue that I am having.

The issue is that there seems to be no way to consolidate dynamic parameters in a table that can be passed into Python.Execute(), without creating another table with Table.FromRecords() or similar, that also works in the report view. See below screenshots:

In the first image, it has clearly worked (the preview shows the Python output, a table from the API).

In the second image, after clicking "Close & Apply", the url is broken and no output shown.

2 things, why is this happening, and how do I best fix it?

[Anonymous]

Update: passing a table in such as 

Query1 = let

Source = Table.FromRecords({
[Name="url", Value="https://jsonplaceholder.typicode.com/todos/"]
})

in

Source

works in both the Editor and the Model - maybe it is an issue with the order of evaluation of queries with #sections? Still wish to use parameters as this exposes the variables in the service, so open to suggestions.