Using the function Web.Contents with more than 2 options

[PowerFabian]

Hello,

i am having an issue trying to manage my sheduled refresh for a web API data source. Due to the fact that Power BI service can't handle hand-authored queries i am trying to rewrite my code to still enable shedule refreshed. Because my datasource has more records than one call to the web API can handle I need to use pagination/paging for my datasource. I know that Web.contents-function only expects one or two options (https://docs.microsoft.com/en-us/powerquery-m/web-contents ) but I need 3 in my case, because my API requires Authorization by a token in the header. So i have the baseURL, the Query option, and my header "Authorization".

Is there a way to use more than 2 options in web.contents or a workaround for my problem that still enables sheduled refresh?

My M language looks like this and gives me an error code for using more than 2 arguments:

let
source = Json.Document(

Web.Contents("https://baseURL.net/external/Sales?$skip=0", [Headers=[Authorization="Static Token Example"]],

[Query= "baseUrl.net/external/Sales?"&#"Anzahl Skip"])),

value = source[value],
#"ConvertToTable" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"ExpandColumn" = Table.ExpandRecordColumn(...... )

in
#"Expand Column"

Hope someone can help me with that.

Regards,

Fabian

[PhilipTreacy]

Hi @PowerFabian 

OK, hope you get it working.

regards

Phil

[PhilipTreacy]

Hi @PowerFabian 

You can use Relative Path and Query in your call like this

api_url = "https://baseURL.net/",
RelPath = "external/Sales",
skip = "0",

data= Json.Document(Web.Contents(api_url,
   [ 
     RelativePath = RelPath,
     Query = [$skip=skip],
     Headers = [#"Authorization"="Static Token Example",#"Content-Type"="application/json"]
   ]
   )
   )

But you appear to be trying to pass 2 query parameters : $skip and Anzahl Skip. I don't understand what Anzahl Skip is meant to do.  The query param is usually of the form ParamName=Value 

With the code as you posted it is trying to apply 2 sets of query parameters, there is one in the base URL and another in the Query record.  If you use the Query record then you shouldn't also pass params in th Base URL. 

Regards

Phil

[PowerFabian]

Hi @PhilipTreacy ,

thank you for help. I will try this and give you feedback if it worked like that. A first test looked good so far and I was able to adjust my code and get multiple data by it.

I tried it with my base URL in my query parameters because i tried to adapt this video and create a function with the query (https://www.youtube.com/watch?app=desktop&feature=youtu.be&v=_SWj249xAxQ).

Regards,

Fabian