Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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!

3 REPLIES 3
v-zhangti
Community Support
Community Support

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
Not applicable

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:

 

Krispin_0-1637826265014.pngKrispin_1-1637826331011.png

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
Not applicable

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.