Forum Discussion

TK12345's avatar
TK12345
Icon for Resolver II rankResolver II
3 years ago
Solved

Dynamic datasource error with relativepath

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'...
Need Help
Show and Tell
  • TK12345's avatar
    TK12345
    3 years ago

    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's avatar
pi_eye
Icon for Resolver IV rankResolver IV
3 years ago

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's avatar
TK12345
Icon for Resolver II rankResolver II
3 years ago

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's avatar
    TK12345
    Icon for Resolver II rankResolver II
    3 years ago

    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's avatar
      pi_eye
      Icon for Resolver IV rankResolver IV
      3 years ago

      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-bi/ 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