Web.Contents with Special Characters in Query Key ($top & $skip)

[trevrobwhite2]

I'm trying to query an OData Feed that does not support server side pagination, therefore I have to send $top and $skip.

This works fine if I modify the URL (I've broken it out of my loop for debugging)

let
            base_url = "https://xx.archerirm.us/contentapi/table?top=1000&$skip=3000",
            Source = Json.Document(Web.Contents(base_url)),
            data = if List.IsEmpty(Source[value]) then -1 else Source[value]
in
    data

However if I publish this to the Power BI Service with my full loop then I cannot refresh the data due to dynamic content, so keeping it simple:

let
    base_url = "https://xxx.archerirm.us/contentapi",
    Source = Json.Document(Web.Contents(base_url, [
        RelativePath="table", 
        Query=[#"$top"=Text.From(1000), #"$skip"=Text.From(3000)
        ], ManualStatusHandling={401}])),
    data = if List.IsEmpty(Source[value]) then -1 else Source[value]
in
    data

Here something is happening to $top & $skip because I'm always getting the first page back

I also tried:

Query=[#"`$top`"=Text.From(1000), #"`$skip`"=Text.From(3000)

But I cannot seem to get those parameters sent over correctly, any ideas?

[trevrobwhite2 · Accepted solution]

After a call with Microsoft and looking at the data with fiddler, we got to the bottom of the issue.

The correct encoding is: #"$top" however this wasn't the issue, it seems when you pass data in Query the key and value get URL Encoded, this meant the data getting sent to the server was:

https://xxx.archerirm.us/contentapi/table?%24top=5&%24skip=3000

I knew this was past the end of the data and after running some tests, it seems the server isn't URL Decoding the data so was completely ignoring the parameters and therefore always returning the first page of data and hence the data never ending.

After a bit of playing it seems ReleativePath does not URL encode the data so the work around was to put the data into the ReleativePath, this is then not treated as a dynamic data source and the server does understand the data (I've reported to the company they need to fix this), but I wanted to include a response here to help others.

My working mini script is now:

let
    base_url = "https://xxx.archerirm.us/contentapi",
    Source = Json.Document(Web.Contents(base_url, [
        RelativePath="table?$top=" & Text.From(1000) & "&$skip=" & Text.From(3000), 
        ManualStatusHandling={401}])),
    data = if List.IsEmpty(Source[value]) then -1 else Source[value]
in
    data

To paginate through all the data and dynamically expand the columns

let
    max_pages = 2000,
    items_per_page = 1000,
    base_url = "https://xxx.archerirm.us/contentapi",
    dataview = "table",

    GetPage = (page as number) as any =>
        let
            skip = page * items_per_page,
            Source = Json.Document(Web.Contents(base_url, [
                RelativePath= dataview & "?$top=" & Text.From(items_per_page) & "&$skip=" & Text.From(skip), 
                ManualStatusHandling={401}])),
            data = if List.IsEmpty(Source[value]) then -1 else Source[value]
        in
            data,
    data_lists = List.Generate(()=>0, each _ < max_pages and GetPage(_) <> -1, each _ + 1, each GetPage(_)),
    data_list = List.Combine(List.Select(data_lists, each _ <> -1)),
    data_table = Table.FromList(data_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    first_row = data_table{0}[Column1],
    fields = Record.FieldNames(first_row),
    #"Expanded Column1" = Table.ExpandRecordColumn(data_table, "Column1", fields, fields)
in
    #"Expanded Column1"

Hopefully this is of help, this works on the RSA Archer interface but should be easily adapted for others, use should be able to put the authentication details in the standard interface as it isn't stored in the script.

[lbendlin]

That's an awesome and somewhat discomforting find. Thank you!

[lbendlin]

let
  base_url = "https://xxx.archerirm.us/contentapi", 
  Source = Json.Document(
    Web.Contents(
      base_url, 
      [RelativePath = "table", Query = [top = "1000", skip = "3000"], ManualStatusHandling = {401}]
    )
  ), 
  data = if List.IsEmpty(Source[value]) then - 1 else Source[value]
in
  data