Dynamic datasource error with relativepath

[TK12345]

Hi all,

I have a problem with a parameter and connecting a GET connector. I have fixed is with hard numbers, but when I add a parameter, I get the error that it uses a dynamic datasource so I can't do schedule refresh. Last time I fixed it with RelativePath. Cause the relative path is after the parameter, I think that that is the problem, but I don't know for sure. How to fix it?

= (offset) =>
let
Source =
Json.Document(
Web.Contents(
"https://" & Number.ToText ( Parameter1 ) & ".xx.xx.online",
[
RelativePath = "/xxxxxx/xxxx/xxxx?take=1000&skip=" & Number.ToText( offset ),
Headers = [
Authorization = "Token " & (Parameter2)
]
]
)
)

How can I change this, so that I don't get the dynamic datasource error, and I can schedule refresh. Good to know, when I change parameter one to hardcoded number, it does work. So parameter 2 has no problem.

[TK12345 · Accepted solution]

For someone with the same problem, I found a solution myself. 

Make a parameter for the whole base URL:
Parameter 1: https://1234.xx.xx.online and add this to the query.

= (offset) =>
let
Source =
Json.Document(
Web.Contents(
Parameter1,
[
RelativePath = "/xxxxxx/xxxx/xxxx?take=1000&skip=" & Number.ToText( offset ),
Headers = [
Authorization = "Token " & (Parameter2)
]
]
)
)

[pi_eye]

Hi, what type is the Parameter1?

It will tell you if you go to Manage parameter and look at the settings there:

I was able to get the above parameter to work, as the type = Text. See below code example (using free API):

let
Source =
Json.Document(
Web.Contents("https://api.coindesk.com/v" & version_number &"/bpi/currentprice.json")
),
bpi = Source[bpi],
USD = bpi[USD]
in
USD

If your parameter must be a number - then you will need to convert to text using an M Query function

So I have specified Type = Decimal Number in the parameter settings, then used the conversion Number.ToText as below:

let
Source =
Json.Document(
Web.Contents("https://api.coindesk.com/v" & Number.ToText(version_number) &"/bpi/currentprice.json")
),
bpi = Source[bpi],
USD = bpi[USD]
in
USD

HTH

Pi

[TK12345]

Hi  Pi,

Thanks for your reply. Parameter1 is a number and comes immediately after the https://. What you told me to do is exactly what I did right?

= (offset) =>
let
Source =
Json.Document(
Web.Contents(
"https://" & Number.ToText ( Parameter1 ) & ".xx.xx.online",
[
RelativePath = "/xxxxxx/xxxx/xxxx?take=1000&skip=" & Number.ToText( offset ),
Headers = [
Authorization = "Token " & (Parameter2)
]
]
)
)

[TK12345]

So if I do it like above, I get the following error in the service:   it uses a dynamic datasource so I can't do schedule refresh

[pi_eye]

Hi TK12345

According to Chris Webb's blog at https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... the service only looks at the first part of the web.contents function  for the dynamic bit.

If you include all dynamic parameters in the relative path, then it will not be detected and should run as usual.

I changed my code to:

let
Source =
Json.Document(
Web.Contents("https://api.coindesk.com/",
[RelativePath="v" & Number.ToText(version_number) &"/bpi/currentprice.json"])
),
bpi = Source[bpi],
USD = bpi[USD]
in
USD

However, your parameter is in the hostname, so I think we would need to use something other than web.contents.

Pi