Help adjusting List.Generate REST function around error "Unable to do a refresh on Web.contents()"

[loginerror]

Hi,

I have seen multiple posts on how to achieve it, but I have trouble adapting my custom REST API function.

Please see below my source function:

(queryID) => let
        resultCount = 1000,
        otherNameForPage = 0,
        URL = "https://baseURL/admin/plugins/explorer/queries/" & Number.ToText(queryID) & "/run",

        GetPage = (otherNameForPage) =>
            let
                content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
                RawData = Json.Document(Web.Contents(
                    URL,
                    [
                        Headers = [ 
                        #"api-username"="myUserName",
                        #"api-key"="myAPIKey",
                        #"Content-Type" = "application/x-www-form-urlencoded"],
                        Content = Text.ToBinary(content1)
                    ]
                    
                ) ),
                resultCount = RawData[result_count]
            in
                if RawData[result_count] = 0 then null else RawData,
            Pages = List.Generate(
                () => [i = 0, RawData = GetPage(i)],
                each [RawData] <> null,
                each [i=[i]+1, RawData = GetPage(i)],
                //each Record.ToTable([RawData])),
                each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
 //           Output = Table.Join(each Pages, Pages[rows])
            Output = Table.Combine(Pages)
in
    Output

With this query, I am able to dynamically fetch my records, page after page, by simply providing the ID of my query on the source website.

I read through these already, but it is not clear to me how the solution should apply to my use case:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/

Some small help to set it correctly wuold be greatly appreciated 🙂

[skins]

So interesting code @loginerror  ! thank you  🙂

is it possible to add this parameter to it "limit=ALL" ?

I tried many combinations but it doesn't work 

thanks again 🙂

[v-easonf-msft]

Hi, @loginerror 

Is the issue in the service or desktop?

If in service,you can consider split the original URL into baseURL and RelativePath.
For more details,please refer to this documen.

Using The RelativePath And Query Options With Web.Contents

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[loginerror · Accepted solution]

I don't like giving up. See my solution below 🙂

(queryID) => let
        resultCount = 1000,
        otherNameForPage = 0,

        GetPage = (otherNameForPage) =>
            let
                content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
                RawData = Json.Document(Web.Contents(
                    "https://baseURL/admin/plugins/explorer/queries",
                    [RelativePath=Number.ToText(queryID) & "/run",
                        Query=
                        [
                            params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
                        ],

                        Headers = [ 
                        #"api-username"="myUsername",
                        #"api-key"="myAPIKey",
                        #"Content-Type" = "application/x-www-form-urlencoded"],
                        Content = Text.ToBinary(content1)
                    ]
                    
                ) ),
                resultCount = RawData[result_count]
            in
                if RawData[result_count] = 0 then null else RawData,
            Pages = List.Generate(
                () => [i = 0, RawData = GetPage(i)],
                each [RawData] <> null,
                each [i=[i]+1, RawData = GetPage(i)],
                //each Record.ToTable([RawData])),
                each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
 //           Output = Table.Join(each Pages, Pages[rows])
            Output = Table.Combine(Pages)
in
    Output