Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
loginerror
Frequent Visitor

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

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 🙂

1 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

View solution in original post

3 REPLIES 3
skins
Frequent Visitor

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
Community Support
Community Support

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.

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors