cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft Peddabomma
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

Accepted Solutions
Microsoft Eric_Zhang
Microsoft

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

@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
Microsoft Eric_Zhang
Microsoft

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

@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

Microsoft Peddabomma
Microsoft

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

Thanks Eric. That was a great help.

Microsoft Peddabomma
Microsoft

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

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.

Microsoft Eric_Zhang
Microsoft

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

@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

Microsoft Peddabomma
Microsoft

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

Hi Eric,

 

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

 

Thanks, Raghu

Microsoft Eric_Zhang
Microsoft

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

@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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors