Forum Discussion
Post request to API does not recognise the posted payload as Json (see python code below)
Hi All, I am trying to connect Power BI with the Piwik Pro API. I have code in Python that is working. I also manage to get the bearer token from the API. Unfortunately when I try to post the query payload the API returns an error because it is not recognised as a Json. The API only allows JSON. The Python code only works with payload
headers = { "Content-Type" : "application/json", json} and
response = requests.post(query_url, headers=headers, json=data)
OR response = requests.post(query_url, headers=headers, data = json.dumps(data)). Please find the working Python code below.
I have tried different approaches, any suggestions would be very helpful.
Not working Power Query Code
let
authKey = "{""grant_type"":""client_credentials"",
""client_id"":""****************************"",
""client_secret"":""*************************""}",
url = "https://shv.piwik.pro",
// Uses the authentication/token method to obtain a token
GetJson = Json.Document(Web.Contents(url,
[Headers= [#"Content-Type"="application/json"],
Content=Text.ToBinary(authKey), RelativePath="/auth/token"])),
AccessToken = GetJson[access_token],
---- It is working till here
body = "{""columns"": [{ ""column_id"": ""website_name""}, { ""column_id"": ""session_entry_url""}],
{ ""format"": ""json""},
{ ""date_from"": ""2020-01-01""},
{ ""date_to"": ""2020-03-01""},
{ ""website_id"": ""a33fcc21-7349-4013-981c-468e1231696e""} ,
{ ""offset"": ""0""},
{ ""limit"": ""100""}
}",
data= Json.Document( Web.Contents(url,
[Headers = [#"Authorization"="Bearer "&AccessToken,
#"Content-Type"="application/json",
#"Accept-Encoding"= "gzip"],
RelativePath="/api/analytics/v1/sessions",
Content = Json.FromValue(body)
]))
in
data
Hi Anonymous
no need to use the advanced editor. Paste it into the formula bar, but make sure that the "=" is replaced as well. Then you're getting valid JSON (with my code) that can be parsed as JSON.
The reason why you're seeing this trash is because your code is being parsed as Csv.Document (see formula in the formula bar)
let authKey = "{""grant_type"":""client_credentials"", ""client_id"":""****************************"", ""client_secret"":""*************************""}", url = "https://shv.piwik.pro", // Uses the authentication/token method to obtain a token GetJson = Json.Document(Web.Contents(url, [Headers= [#"Content-Type"="application/json"], Content=Text.ToBinary(authKey), RelativePath="/auth/token"])), AccessToken = GetJson[access_token], //// This is now valid JSON and should technically work (provided the format fits to the API body = "[{""columns"": [{ ""column_id"": ""website_name""}, { ""column_id"": ""session_entry_url""}]}, #(cr)#(lf) { ""format"": ""json""}, #(cr)#(lf) { ""date_from"": ""2020-01-01""}, #(cr)#(lf) { ""date_to"": ""2020-03-01""}, #(cr)#(lf) { ""website_id"": ""a33fcc21-7349-4013-981c-468e1231696e""} ,#(cr)#(lf) { ""offset"": ""0""}, #(cr)#(lf) { ""limit"": ""100""} #(cr)#(lf)]", data= Json.Document( Web.Contents(url, [Headers = [#"Authorization"="Bearer "&AccessToken, #"Content-Type"="application/json", #"Accept-Encoding"= "gzip"], RelativePath="/api/analytics/v1/sessions", Content = Json.FromValue(body) ])) in data
5 Replies
Hi Anonymous
your JSON is not valid. You've mixed up some brackets. The JSON that your code returns it this:
{"columns": [{ "column_id": "website_name"}, { "column_id": "session_entry_url"}],
{ "format": "json"},
{ "date_from": "2020-01-01"},
{ "date_to": "2020-03-01"},
{ "website_id": "a33fcc21-7349-4013-981c-468e1231696e"} ,
{ "offset": "0"},
{ "limit": "100"}
}and you (probably) need this?:
[{"columns": [{ "column_id": "website_name"}, { "column_id": "session_entry_url"}]},
{ "format": "json"},
{ "date_from": "2020-01-01"},
{ "date_to": "2020-03-01"},
{ "website_id": "a33fcc21-7349-4013-981c-468e1231696e"} ,
{ "offset": "0"},
{ "limit": "100"}
]Hi ImkeF , Thanks for your reply! The advanced editor does not accept it like this:
I only get it accepted like this:
body = "{""columns"":[{ ""column_id"": ""website_name""}, { ""column_id"": ""session_entry_url""}],
{ ""format"": ""json""},
{ ""date_from"": ""2020-01-01""},
{ ""date_to"": ""2020-03-01""},
{ ""website_id"": ""a33fcc21-7349-4013-981c-468e1231696e""} ,
{ ""offset"": ""0""},
{ ""limit"": ""100""}}",But if you look in the query editor it does not look like a json:
json2 =Json.FromValue(body),
#"Imported CSV" = Csv.Document(json2,[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),Thanks a lot.
Kind Regards, Linv
Hi Anonymous
no need to use the advanced editor. Paste it into the formula bar, but make sure that the "=" is replaced as well. Then you're getting valid JSON (with my code) that can be parsed as JSON.
The reason why you're seeing this trash is because your code is being parsed as Csv.Document (see formula in the formula bar)
let authKey = "{""grant_type"":""client_credentials"", ""client_id"":""****************************"", ""client_secret"":""*************************""}", url = "https://shv.piwik.pro", // Uses the authentication/token method to obtain a token GetJson = Json.Document(Web.Contents(url, [Headers= [#"Content-Type"="application/json"], Content=Text.ToBinary(authKey), RelativePath="/auth/token"])), AccessToken = GetJson[access_token], //// This is now valid JSON and should technically work (provided the format fits to the API body = "[{""columns"": [{ ""column_id"": ""website_name""}, { ""column_id"": ""session_entry_url""}]}, #(cr)#(lf) { ""format"": ""json""}, #(cr)#(lf) { ""date_from"": ""2020-01-01""}, #(cr)#(lf) { ""date_to"": ""2020-03-01""}, #(cr)#(lf) { ""website_id"": ""a33fcc21-7349-4013-981c-468e1231696e""} ,#(cr)#(lf) { ""offset"": ""0""}, #(cr)#(lf) { ""limit"": ""100""} #(cr)#(lf)]", data= Json.Document( Web.Contents(url, [Headers = [#"Authorization"="Bearer "&AccessToken, #"Content-Type"="application/json", #"Accept-Encoding"= "gzip"], RelativePath="/api/analytics/v1/sessions", Content = Json.FromValue(body) ])) in data