Service Refresh issue - web.contents with API key and parameters in the URL

[Anonymous]

Hello,

Firstly, I have gone through a multitude of blog posts and forum entries, but could not apply the resolution to my case, so I would really appreciate some guidance from you, more experienced folks.

In Short, I have an API call that retrieves a token, then a second one that returns a table and this works fine in PBI desktop, but on service I am unable to create a dataflow or schedule a refresh, as the URL has parameters and it is not static, when the scan happens.

This is how my original query bit  with the second call that returns the table, using the key from the first call (working on desktop):

Value in the Header bit is the API key; StartDate_string & EndDate_string are from - to date parameters

BASEURL marks where the URL is, as I cannot provide that here.

Source2 = Json.Document(Web.Contents("BASEURL?dataType=Futures&startDate=" & StartDate_string & "&endDate=" & EndDate_string & "&includeProperties=ALL", [Headers=[Cookie="JSESSIONID="&Value , Accept="application/json"]])),

This is how I attempted to rewrite it for PBI service. I am not even sure if I needed to split the URL at all (and whether my split is correct), in order to use the RelativePath argument, but it seemed like it would not work otherwise.

Source2 = Json.Document(
Web.Contents("BASEURL",
[
RelativePath="SECOND_URLBIT",
Query=
[
dataType="Futures",
startDate=StartDate_string,
endDate=EndDate_string,
includeProperties="ALL"
]
]
), [Headers=[Cookie="JSESSIONID="&Value , Accept="application/json"]])),

I would greatly appreciate any help, also any suggestion if this approach is not the best, as I have not used API connections much this far. The current error message that I receive is the following, although I am positive, the re-building I am attempting is also not flawless.

Expression.Error: We cannot convert a value of type Record to type Number.
Details:
Value=[Record]
Type=[Type]

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You don't need to put "Headers" out of Web.Contents(). Like "RelativePath" and "Query", "Headers" is also a parameter of Web.Contents().

Here is the document for more details.

https://docs.microsoft.com/en-us/powerquery-m/web-contents

According to this document, "Headers" will return a record, I think this is the reason for showing "We cannot convert a value of type Record to type Number".

So you could refer to the following codes:

Source2 = Json.Document(
Web.Contents("BASEURL",
   [
    RelativePath="SECOND_URLBIT",
    Query=
          [
           dataType="Futures",
           startDate=StartDate_string,
           endDate=EndDate_string,
           includeProperties="ALL"
          ],
    Headers=
          [
           Cookie="JSESSIONID="&Value, 
           Accept="application/json"
          ]
   ]
   ))