API with Python Returns Nothing

[ERIC_A]

Hello I am trying to get a power BI connection to an intranet website to manipulate some data and put it in a table in powerBI. 

My python script works fine in Visual studio code but I don't know why the PBI wizard doesn't detect the text. 

below my script

import http.client
import pandas as pd
import io
import json
import requests

conn = http.client.HTTPSConnection("mana-nic.internal.ridol.com")

payload = "{\"fromdate\":\"2022-07-22T12:30:59.422Z\",\"todate\":\"2022-07-23T12:29:59.422Z\",\"hourfilter\":-1,\"customer\":\"TAO\",\"filters\":[{\"id\":\"NetworkElementID\",\"value\":\"LALA\"}],\"sortby\":[{\"id\":\"customer\",\"desc\":true}],\"sites\":null,\"selectedbox\":null,\"currentpage\":0,\"pagesize\":25,\"teams\":\"A\",\"flagged\":false,\"dump\":true}"

headers = {
'User-Agent': "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:91.0) Gecko/20100101 Firefox/91.0",
'Accept': "application/json, text/plain, */*",
'Accept-Language': "en-US,en;q=0.5",
'Content-Type': "application/json;charset=utf-8",
'Origin': "https://mana-nic.internal.ridol.com",
'DNT': "1",
'Connection': "keep-alive",
'Referer': "https://mana-nic.internal.ridol.com/",
'Cookie': "_ga_G9EF4FD5HD=GS1.1.16572475.1.0.1657672481.0; _ga=GA1.1.13304435.1657672475; SESSa35220c099aa73e7b196fbe8b6ddcb=oz-aPoaf_nl4qoOJ3RcigEjuuhr465iuJboj0KM3U",
'Sec-Fetch-Dest': "empty",
'Sec-Fetch-Mode': "cors",
'Sec-Fetch-Site': "same-origin"
}

conn.request("POST", "/apiserver/nic/dashboard/content", payload, headers)

res = conn.getresponse()
data = res.read()
decodeData = data.decode("utf-8")
print(decodeData)

How can I make the PBI detect the text? 

thank you for your help 

[ERIC_A]

hi 

yes the reason is my limited knowledge. this is what I have came up so far. Anyways I solved this issue by adding a couple of lines.

pbiTable = pd.read_csv(io.StringIO(decodeData))

print(pbiTable)

However this python script won't run on schedule without personal gateway so I will need to find another way to fetch this data without python. Do you know if there is any option I can use to replace this python option? 

[lbendlin]

As I mentioned, the regular Web.Contents() call should be able to fetch that data as well, and it can then also run via an Enterprise Gateway.  You may need to slightly reformat your payload but the rest is straightforward.  Unfortunately there is not much else I can help you with since I don't have access to your API endpoint.

[ERIC_A]

hi 

do you know how I can debug the errors I am getting with my code? I have been trying to find out why I get a bad request 400.  I noticed in a SW called insomnia that if I remove any parameter from the payload I get a message from the server saying what is expecting to receive but with power BI all I get is bad request 

Details:
DataSourceKind=Web

Here is my code

let
payload = "[{
""fromdate"":""2022-07-22T12:30:59.422Z"",
""todate"":""2022-07-23T12:29:59.422Z"",
""hourfilter"":-1,
""customer"":""T"",
""filters"":[{""id"":""NetworkElementID"",""value"":""9J""}],
""sortby"":[{""id"":""customer"",""desc"":true}],
""sites"":null,
""selectedbox"":null,
""currentpage"":0,
""pagesize"":25,
""teams"":""NI"",
""flagged"":false,
""dump"":true
}]",
url = "https://internal.com/apiserver/nic/dashboard/content",
Source =Json.Document(Web.Contents(url,[Content=Text.ToBinary(payload)]))

in
Source

Thank you ! 

[ERIC_A]

I made it work! 

to debug the issues I added a Web.Contents(url, [ManualStatusHandling={400..499}, Content=payload]) and then I was adding line by line in my payload and debugging the errors 

It turns out that the syntax for empty filters changes from [] to {} and also the syntax for list inside the payload changed from [A="X",B="Y"] to {[A="X",B="Y"]}

I used to code in borland c++ and it was easy to debug. now wit these new technologies I struggle specially with json syntax.

fromdate= "2022-07-22T12:30:59.422Z",
todate="2022-07-23T12:29:59.422Z",
sortby={[id="customer",desc=true]},
filters={},
hourfilter=-1,
customer="T",
sites=null,
selectedbox=null,
currentpage=0,
pagesize=25,
teams="NI",
flagged=false,
dump=true
]

[ERIC_A]

No worries you gave me enough information to find the solution. thank you so much for your help!

[lbendlin]

Any particular reason you are using a Python script instead of the native Web.Contents() call?

[NvanOort]

Hi Ibendlin,

I am trying the same as ERIC_A. I have to use a Python Script because I want to do a PUT request for retrieving a list of data from an API. The Web.Content function only supports GET and POST requests. 

The strange part for me is that the PUT request works in VS Code. However, when I copy the code to Python Script in Power Query, I get an empty folder... No errors or something like that. It starts loading data by showing a Python folder and when it expands there isn't any data showing.

This is my Python script (replaced sensitive data for '...'):

import requests 

url = "..."
data = {
        "filters": {
            "date_time_from": "...",
            "date_time_to": "...",
            "driver_id": [
                ...
            ]
        }    
    }

headers = {
    "Content-Type": "application/json",
    "Token": "..."
}

response = requests.put(url, json=data, headers=headers)

if response.status_code == 200:
    print("PUT request successful")
    print("Response:", response.json())
else:
    print("PUT request failed")
    print("Status code:", response.status_code)
    print("Response:", response.text)

 
Do you have any clue why I get this as a result?

Kind regards.

[lbendlin]

You are printing too much.  Only return the result.

[NvanOort]

Thanks for your quick response!

Still no data unfortunately. Only using this part for the result now:

print(response.json())

Got an error message earlier that Matplotlib and Pandas was not installed. Done that in the meantime and to be sure I just tested the script by addring those libraries as import but that does not help eighter.

Sorry, I'am new to Python.

[ERIC_A]

Hi,

I remember the issue was the syntax of filtering. This is what was causing my code to return an empty object. see below:

It turns out that the syntax for empty filters changes from [] to {} and also the syntax for list inside the payload changed from [A="X",B="Y"] to {[A="X",B="Y"]}

[lbendlin]

Use Python in Power BI Power Query Editor - Power BI | Microsoft Learn