Forum Discussion
How to pass the request object as query string in Power Query Formula Language
Hi Have the below Error while passing a request odject as query string.Please help.
Code:
let
authKey = "Token jsf06vmN/d0PU2sad2hu3NdjSIJZr=z/ JG/**bleep**iZcFZPVAsAXmN+d20000",
url = "https://XXXXXXXXXX.com/api/v1/query?query= ""dataset"": ""mscom_prod"",""queries"": [ {""type"": ""single_measurement"",""measure"": {""aggregator"": ""unique_count"",""column"": ""mc1_visitor_id""}} ], ""start"":1451638800000,""end"":1468430640000,""max_groups"":0,""group_by"":[""ms_prod""]",
GetJson = Json.Document(Web.Contents(url,[Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"]]))
in
GetJson
ERROR:
DataSource.Error: Web.Contents failed to get contents from 'https://XXXXXXXX.com/api/v1/query?query=%20%22dataset%22:%20%22mscom_prod%22,%22queries%22:%20%5B%20%7B%22type%22:%20%22single_measurement%22,%22measure%22:%20%7B%22aggregator%22:%20%22unique_count%22,%22column%22:%20%22mc1_visitor_id%22%7D%7D%20%5D,%20%22start%22:1451638800000,%22end%22:1468430640000,%22max_groups%22:0,%22group_by%22:%5B%22ms_prod%22%5D' (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://XXXXXXX.com/api/v1/query
Url=https://XXXXXXXX.com/api/v1/query?query=%20%22dataset%22:%20%22mscom_prod%22,%22queries%22:%20%5B%20%7B%22type%22:%20%22single_measurement%22,%22measure%22:%20%7B%22aggregator%22:%20%22unique_count%22,%22column%22:%20%22mc1_visitor_id%22%7D%7D%20%5D,%20%22start%22:1451638800000,%22end%22:1468430640000,%22max_groups%22:0,%22group_by%22:%5B%22ms_prod%22%5D
How is extracting data from the JSON going on? If there's any problem, feel free to post. If no further question, please accept one reply as solution to close this thread. :)
6 Replies
- Make sure the DataSourcePath=https://mscom.interana.com/api/v1/query is correct.
- Make sure "https://XXXXXXXXXX.com/api/v1/query?query="'s bold part is a valid parameter name in that path.
- The passed value of the query seems not a valid json format, try to fix it. json validator
A simple demo for your reference. Connection to an API with Power Query
Thanks Eric. That was a great help.
Hi Eric ,
I am now able to connect to the API . However my responce in in below format.
{"rows": [{"values": [["skype"], 6990480.0]}, {"values": [["*null*"], 70280640.0]}, {"values": [["office"], 11148624.0]}, {"values": [["sql-non-specified"], 323136.0]}, {"values": [[".net-framework-4.6"], 632352.0]}, {"values": [["sql-server-2016"], 247392.0]}, {"values": [["visual-studio-dev14"], 541824.0]}, {"values": [["azure"], 325392.0]}, {"values": [["msn games"], 289680.0]}, {"values": [["windows7"], 651264.0]}, {"values": [["All others"], 5956896.0]}], "columns": [{"type": "array", "label": ["ms_prod"]}, {"type": "number", "label": "measure_value"}]}
How do i convert this to a table format in PBI desktop .
When i am impoting JSON i am getting the columns and rows in below format.
How do i convert the Rows list and columns list into a table format ?
Thanks in advance.