Post request to API does not recognise the posted payload as Json (see python code below)

[LinVPowerBI1]

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

[ImkeF]

Hi @LinVPowerBI1 

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"}
]

[LinVPowerBI1]

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

[ImkeF · Accepted solution]

Hi @LinVPowerBI1 

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

[LinVPowerBI1]

Hi @ImkeF

Thanks for your fast reply. It looks like the Json is working but I am still stuck. 

 

 

I got the same question when retrieving the access token, but when I click connect it works.

Also In python it works: 

def query_request(data):

   query_url = piwik_path + "api/analytics/v1/sessions/"

   headers = {

   "Content-Type" : "application/json",

   "Accept-Encoding": "gzip",

   "Authorization" : "Bearer %s" % str(get_token())

   }

   response = requests.post(query_url, headers=headers, json=data) 

   return (response.text)

Result:

 

I really don't understand what I am missing here. 

THanks, LinV

[ImkeF]

Hi @LinVPowerBI1  

hard to tell. Only thing that comes to my mind is: Try to omit the leading "/" in the relative path:

RelativePath="api/analytics/v1/sessions",

instead of:

RelativePath="/api/analytics/v1/sessions",