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

[Peddabomma]

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...' (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...

[Eric_Zhang]

@Peddabomma

1. Make sure the DataSourcePath=https://mscom.interana.com/api/v1/query is correct.
2. Make sure "https://XXXXXXXXXX.com/api/v1/query?query="'s bold part is a valid parameter name in that path.
3. 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

[Peddabomma]

Thanks Eric. That was a great help.

[Peddabomma]

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.

[Eric_Zhang]

@Peddabomma

Could you share how did you manage to make the API work? What was wrong?

To convert the json to a table, I think you can reference the power query below. I am using a JSON file other than web API, however the transformations are in the same way, you can just replace the source accordingly.

let
    Source = Json.Document(File.Contents("C:\test\json.json")),
    rows = Source[rows],
    #"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Added Index", "Column1", {"values"}, {"Column1.values"}),
    #"Expanded Column1.values" = Table.ExpandListColumn(#"Expanded Column1", "Column1.values"),
    #"Added Column" = Table.AddColumn(#"Expanded Column1.values", "isValue", each
      let
        result = try Number.From([Column1.values]) otherwise "Not value",
        resultType = if result = "Not value" then "N" else "Y"
      in
        resultType
    ),
    #"Filtered Rows" = Table.SelectRows(#"Added Column", each ([isValue] = "N")),
    #"Expanded Column1.values1" = Table.ExpandListColumn(#"Filtered Rows", "Column1.values"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Column1.values1",{"Index"},#"Added Column",{"Index"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Column1.values", "isValue"}, {"NewColumn.Column1.values", "NewColumn.isValue"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded NewColumn", each ([NewColumn.isValue] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "isValue", "NewColumn.isValue"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.values", "ms_prod"}, {"NewColumn.Column1.values", "measure_value"}})
    
in
    #"Renamed Columns"

[Peddabomma]

Hi Eric,

There was a syntax error in my query after fixing that i was able to connect to the API.

Thanks, Raghu

[Eric_Zhang · Accepted solution]

@Peddabomma

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. 🙂