cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peddabomma
Microsoft
Microsoft

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

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

View solution in original post

6 REPLIES 6
Eric_Zhang
Microsoft
Microsoft

@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

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.

 

1.JPG2.JPG

 

How do i convert the Rows list and columns list into a table format ?

 

Thanks in advance.

@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"

Capture.PNG

Hi Eric,

 

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

 

Thanks, Raghu

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

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors