CODEWITHSUNDEEP
apirestpowerbipowerquery
Santosh
Santosh

Reputation: 2505

How to pass the request object as query string in Power Query Formula Language

I am trying to pull data from an API in power bi using power query formula language.

My code is:

let
obj= "{ ""dataset"": ""mydataset"",""queries"": [ { ""type"": ""single_measurement"", ""measure"": { ""aggregator"": ""unique_count"", ""column"": ""visitor_id"" } } ], ""start"":1451638800000,""end"":1468430640000,""max_groups"":1000,""group_by"":[""extrhike""]}",
authKey = "Token js+JG/FaGiZcFZPVAsAXmN+d20000",
url = "https:// myhostaddress.cloudapp.azure.com/api/v1/query?query=obj",

GetJson = Json.Document(Web.Contents(url,[Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"]]))

in
GetJson

I want to pass the data request object(obj) as a query string like following

https://myhostaddress.cloudapp.azure.com/api/v1/query?query={ "dataset": "mydataset","queries": [ { "type": "single_measurement", "measure": { "aggregator": "unique_count", "column": "visitor_id" } } ], "start":1451638800000,"end":1468430640000,"max_groups":1000,"group_by":["extrhike"]}

I am unable to pass obj as a querystring value in power query.I am getting following error

DataFormat.Error: Invalid URI: The hostname could not be parsed.
Details:
https:// myhostaddress.cloudapp.azure.com/api/v1/query?query=obj

How do I pass the request object(obj) as query string in Power Query Formula Language.

Thanks in advance

Upvotes: 1

Views: 2221

Answers (4)

Sergey Lossev
Sergey Lossev

Reputation: 1530

You also may use this

It escapes obj to percent-string and appends to "query" parameter

let
    obj= "{ ""dataset"": ""mydataset"",""queries"": [ { ""type"": ""single_measurement"", ""measure"": { ""aggregator"": ""unique_count"", ""column"": ""visitor_id"" } } ], ""start"":1451638800000,""end"":1468430640000,""max_groups"":1000,""group_by"":[""extrhike""]}",
    authKey = "Token js+JG/FaGiZcFZPVAsAXmN+d20000",
    url = "https:// myhostaddress.cloudapp.azure.com/api/v1/query?query="&Uri.EscapeDataString(obj),

    GetJson = Json.Document(Web.Contents(url,[Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"]]))
in
    GetJson

Upvotes: 0

Sergey Lossev
Sergey Lossev

Reputation: 1530

Try to use this

It POSTs content instead of GETting query

let
    obj = "{ ""dataset"": ""mydataset"",""queries"": [ { ""type"": ""single_measurement"", ""measure"": { ""aggregator"": ""unique_count"", ""column"": ""visitor_id"" } } ], ""start"":1451638800000,""end"":1468430640000,""max_groups"":1000,""group_by"":[""extrhike""]}",
    authKey = "Token js+JG/FaGiZcFZPVAsAXmN+d20000",
    url = "https:// myhostaddress.cloudapp.azure.com/api/v1/query",

    GetJson = Json.Document(Web.Contents(url,[
        Headers = [#"Authorization"=authKey, #"Content-Type"="application/json"],
        Content = Text.ToBinary(obj)
    ]))
in
    GetJson

Upvotes: 0

Richard Warriner
Richard Warriner

Reputation: 11

Have you tried passing in your query parameters with the Query argument of Web.Contents ?

For e.g

let
    Url = "https:// myhostaddress.cloudapp.azure.com"
    Request = Json.Document(Web.Contents(url,
                                             [Headers = 
                                                      [#"Authorization"=authKey, 
                                                       #"Content-Type"="application/json"],
                                             RelativePath = "/api/v1/query",
                                             Query = [
                                                         <insert record here of your query object>
                                                     ]
                                             ]

An example of the Query record might be:

Query = [#"Argument 1" = "Foo", #"Argument 2" = "Bar", limit = "1000", skip="500"]

You can read about the options here: https://msdn.microsoft.com/en-us/library/mt260892.aspx

Upvotes: 1

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

You need to add the strings together. It should be url = "https:// myhostaddress.cloudapp.azure.com/api/v1/query?query=" & obj,.

Upvotes: 0

Related Questions