cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Load JSON as source via Python

I'm trying to get the JSON from Python as Source in the Query Editor:

 

let
    Source = Python.Execute("import urllib.request, json #(lf)with urllib.request.urlopen(""https://api.pipedrive.com/v1/organizations?start=0&limit=50&api_token=MyVerySecretGUID"") as url:#(lf)    data = json.loads(url.read().decode())#(lf)    print(data)"),
    data = Source,
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"Column1.id", "Column1.name"})
in
    #"Expanded Column1"

Tried different variations without the converting to table steps. But the Source step does not even returns the respronse. 

Which format does Power BI want to be delivered? Tried print, clean JSON everything even to get the child items out of the JSON nothing works...

 

The Python code returns the JSON directly:

import urllib.request, json 
with urllib.request.urlopen("https://api.pipedrive.com/v1/organizations?start=0&limit=50&api_token=MyVerySecretGUID") as url:
    data = json.loads(url.read().decode())
    print(data)

 

p.s. yes I know I can get the JSON directly as source, but I want to loop trough all records provided and no user actions needs to be involved(like the Parameters/Invoked function). That's why im trying to do it via Python. 

 

Am I doing something wrong here?

 

10 REPLIES 10
Highlighted
Community Support
Community Support

Re: Load JSON as source via Python

Hi Laho,

 

Power query uses "dataset" as input, "output" as output to interactive with R or Python script, so you can modify python code as below and check if it can work:

import urllib.request, json 
with urllib.request.urlopen("https://api.pipedrive.com/v1/organizations?start=0&limit=50&api_token=MyVerySecretGUID") as url:
    data = json.loads(url.read().decode())
output = data

Or in another way, you can simply use function Web.Contents() to achieve data from the website.

 

Regards,

Jimmy Tao

 

Highlighted
Helper I
Helper I

Re: Load JSON as source via Python

Hi Jimmy,

 

Thanks, changed the code according to your suggestion. The navigator still does not sees any table content. The strange thing is that in Python itself the code returns the JSON perfectly.

 

 

The thing is that i want to ittirate over my list and do a foreach api request. That's why i cant use a default web.contents().

Highlighted
Community Support
Community Support

Re: Load JSON as source via Python

Hi Laho,

 

Maybe you can delete the json module and try code below, then check if it can meet your requirement.

import urllib.request
with urllib.request.urlopen("https://api.pipedrive.com/v1/organizations?start=0&limit=50&api_token=MyVerySecretGUID") as url:
    data = url.read().decode("utf-8")
output = data

 Regards,

Jimmy Tao

Highlighted
Helper I
Helper I

Re: Load JSON as source via Python

Hi Jimmy,

 

Same result... 

 

Laurens,

Highlighted
Anonymous
Not applicable

What triggers the Python.Execute function to output data to the Query Editor?

I started with the challenge as Laho (loading JSON responses from a REST API into Power BI Desktop via Python), and tried to break down the issue to see if I could get any output from the Connector.

 

The following tests all return the same empty Table with "Name" / "Value" columns:

var = 0
print(var)
var = 0
output = var
var = 0
var

So the base question here: What triggers the Python.Execute function to output data to the Query Editor?

 

I also tried just viewing the internal documentation on Python.Execute (Blank Query, erase everything but the function name itself: "Python.Execute") but it doesn't give any samples or indicate either.

Highlighted
Anonymous
Not applicable

Re: What triggers the Python.Execute function to output data to the Query Editor?

Not the solution we're seeking, but this works in Power Query:

let
    WhereIsMyData = "C:\Users\<username>\Desktop\py2pbi_test.json",
    PythonCode =
        Python.Execute(
            "import json
            #(lf)import write2File #custom library I made to output files
            #(lf)raw_data = {""key"":""value""}
            #(lf)pretty_json_data = json.dumps(raw_data, indent=2)
            #(lf)output_path = " & WhereIsMyData & "
            #(lf)write2File.io(pretty_json_data,output_path)"
            ,[]
        ),
    GetJson = 
        Json.Document(
            File.Contents(WhereIsMyData)
        )
in
    GetJson

 

Highlighted
Helper I
Helper I

Re: What triggers the Python.Execute function to output data to the Query Editor?

Hi Vishneskij,

 

Do you maybe have any solution how to fetch/display the correct data of Python.Execute?

I'm stuck atm and out of ideas..

 

Laurens

Highlighted
Regular Visitor

Re: What triggers the Python.Execute function to output data to the Query Editor?

Hi @Laho Laho - I'm having the same issue.  Did you find a solution to be able to use the "Python Script" as data source and import JSON data from REST API?

Highlighted
Helper I
Helper I

Re: What triggers the Python.Execute function to output data to the Query Editor?

No, but currently i've managed to create a workaround this way;

 

let
PageSize = 500,
GetPageData = (page) =>

let

Response = Json.Document(
Web.Contents(
"https://api.pipedrive.com/v1/deals",
[Query=[start=Number.ToText(PageSize * page), limit=Number.ToText(PageSize), api_token="0123456789"]]
)
),
Data = Response[data]

in

Data,
GetPageRecord = (previousPageRecord) =>

let

PageNumber = previousPageRecord[PageNumber] + 1,

PageData = GetPageData(PageNumber),

Result = [PageNumber=PageNumber, PageData=PageData]

in

Result

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors