help iterate trhough webpages till no webpage

[Paul_keijzers]

Hi 

I am trying to loop trough webpages and have this code below but it gives me a error on next.. so i am doing something wrong here. I hope someone can help me fix it. And it would be great if i can schedule refres in the end.

Below the query i am using:

let

    Source = List.Generate( ()=>
[Result = Json.Document(Web.Contents("https://tenantx.api-us1.com/api/3/accounts", [Headers=[#"Api-Token"="123456789"]])), Counter=0, Next = Json.Document(Web.Contents("https://tenantx.api-us1.com/api/3/accounts", [Headers=[#"Api-Token"="123456789"]]))[next]],
each [Next] <> null,
each [
Next = [Result][next],
Result = let
        next = [Result][next],
        offset = Text.BetweenDelimiters( next, "offset=", "&"),
        limit = Text.AfterDelimiter(next, "limit="),
        StartWithEmptyTable = Json.Document(Web.Contents("https://tenantx.api-us1.com/api/3/accounts/?offset=0&limit=20", [Query=[offset=Text.From(offset), limit=Text.From(limit)]],[Headers=[#"Api-Token"="123456789"]]))
    in
        StartWithEmptyTable,
Counter = [Counter] + 1
]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result", "Counter"}, {"Result", "Counter"}),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"count", "next", "previous", "results"}, {"count", "next", "previous", "results"}),
    #"Expanded results" = Table.ExpandListColumn(#"Expanded Result", "results"),
    #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"name", "url"}, {"name", "url"})
in
    #"Expanded results1"

and this is the result i get when using postman to query directly.

{
    "accounts": [
        {
            "name": "Rivalss",
            "accountUrl": "",
            "createdTimestamp": "2021-03-22T13:41:41-05:00",
            "updatedTimestamp": "2021-03-22T13:41:42-05:00",
            "contactCount": "0",
            "dealCount": "0",
            "links": {
                "notes": "https://tenantx.api-us1.com/api/3/accounts/4/notes",
                "accountCustomFieldData": "https://tenantx.api-us1.com/api/3/accounts/4/accountCustomFieldData",
                "accountContacts": "https://tenantx.api-us1.com/api/3/accounts/4/accountContacts",
                "emailActivities": "https://tenantx.api-us1.com/api/3/accounts/4/emailActivities",
                "contactEmails": "https://tenantx.api-us1.com/api/3/accounts/4/contactEmails"
            },
            "id": "4"
        },
        {
            "name": "Joske",
            "accountUrl": null,
            "createdTimestamp": "2021-03-23T11:32:05-05:00",
            "updatedTimestamp": "2021-03-23T11:32:05-05:00",
            "contactCount": "0",
            "dealCount": "0",
            "links": {
                "notes": "https://tenantx.api-us1.com/api/3/accounts/9/notes",
                "accountCustomFieldData": "https://tenantx.api-us1.com/api/3/accounts/9/accountCustomFieldData",
                "accountContacts": "https://tenantx.api-us1.com/api/3/accounts/9/accountContacts",
                "emailActivities": "https://tenantx.api-us1.com/api/3/accounts/9/emailActivities",
                "contactEmails": "https://tenantx.api-us1.com/api/3/accounts/9/contactEmails"
            },
            "id": "9"
        },
        {
            "name": "spxvb",
            "accountUrl": "",
            "createdTimestamp": "2021-03-24T02:33:42-05:00",
            "updatedTimestamp": "2021-03-24T02:33:43-05:00",
            "contactCount": "0",
            "dealCount": "0",
            "links": {
                "notes": "https://tenantx.api-us1.com/api/3/accounts/10/notes",
                "accountCustomFieldData": "https://tenantx.api-us1.com/api/3/accounts/10/accountCustomFieldData",
                "accountContacts": "https://tenantx.api-us1.com/api/3/accounts/10/accountContacts",
                "emailActivities": "https://tenantx.api-us1.com/api/3/accounts/10/emailActivities",
                "contactEmails": "https://tenantx.api-us1.com/api/3/accounts/10/contactEmails"
            },
            "id": "10"
        },
        {
            "name": "yep",
            "accountUrl": null,
            "createdTimestamp": "2021-03-24T13:59:49-05:00",
            "updatedTimestamp": "2021-03-24T13:59:49-05:00",
            "contactCount": "0",
            "dealCount": "0",
            "links": {
                "notes": "https://tenantx.api-us1.com/api/3/accounts/13/notes",
                "accountCustomFieldData": "https://tenantx.api-us1.com/api/3/accounts/13/accountCustomFieldData",
                "accountContacts": "https://tenantx.api-us1.com/api/3/accounts/13/accountContacts",
                "emailActivities": "https://tenantx.api-us1.com/api/3/accounts/13/emailActivities",
                "contactEmails": "https://tenantx.api-us1.com/api/3/accounts/13/contactEmails"
            },
            "id": "13"
        },
        {
            "name": "Org",
            "accountUrl": null,
            "createdTimestamp": "2021-03-29T01:34:02-05:00",
            "updatedTimestamp": "2021-03-29T01:34:02-05:00",
            "contactCount": "0",
            "dealCount": "0",
            "links": {
                "notes": "https://tenantx.api-us1.com/api/3/accounts/2175/notes",
                "accountCustomFieldData": "https://tenantx.api-us1.com/api/3/accounts/2175/accountCustomFieldData",
                "accountContacts": "https://tenantx.api-us1.com/api/3/accounts/2175/accountContacts",
                "emailActivities": "https://tenantx.api-us1.com/api/3/accounts/2175/emailActivities",
                "contactEmails": "https://tenantx.api-us1.com/api/3/accounts/2175/contactEmails"
            },
            "id": "2175"
        }
    ],
    "meta": {
        "total": "9425"
    }
}

[v-kelly-msft]

Hi  @Paul_keijzers ,

After researching, you could refer to below blog:

Loop through Multiple Web Pages using Power Query

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Paul_keijzers]

Thank you for your reply but here i need to state the rows upfront that i want to query. As the rows are expanding each day it needs to be variable.

In C# you have a function for each that would be interesting to have here as well for each till max meta.total

[v-kelly-msft · Accepted solution]

Hi  @Paul_keijzers ,

Based on my understanding,if you could get all the data from all the webpages and the data can be refreshed automatically,then you will realize" iterate trhough webpages till no webpage",check below:

https://social.technet.microsoft.com/Forums/en-US/0f9fec02-4469-4c56-92ec-00d46b26b3fc/how-to-automa...

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=1c86e2d9-...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Paul_keijzers]

I think this would work but have found a new way by just updating the offset. Thanks!