Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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,
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.
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
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |