Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
}
}
Solved! Go to 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:
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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!
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
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:
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
I think this would work but have found a new way by just updating the offset. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |