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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Laho
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
v-yuta-msft
Community Support
Community Support

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

 

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().

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

Hi Jimmy,

 

Same result... 

 

Laurens,

Anonymous
Not applicable

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.

Anonymous
Not applicable

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

 

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

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?

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

@Laho - thanks for sharing, I'll give it a try!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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