Forum Discussion
Struggling with the cyclic reference error
Your first steps are correct
let userCount = Json.Document(Web.Contents("https://storfolloikt.pureservice.com/agent/api/user/count", [ Headers=[ Accept="application/vnd.api+json", #"X-Authorization-Key"= Text.From(#"PUSapikey") ] ]))[count], pages = Number.RoundUp(userCount / 500),but then you veer off track. Next step is to create a list with all the URLs you need to fetch the data chunks.
since you already know the number of pages you need there is not really a reason to use list.generate any more. A simple
{1..pages}
is enough. Convert that to a table, add your GetUserData function as a custom column, and combine the output.
Ok, so I am trying List.Generate, but for some reason my resulting list seems endless. The function GetUserData performs a web call to return a list of Users (one page = 500 items). So I want to call this function with multiple pages. However, as I said, it seems not to stop.
Perhaps my lack of understanding of variable scope - for example, that I must defined pageNumber outside the List.Generate() construct, otherwise I get an error. Any advice and enlightment on scoping is greatly appreciated!
let
userCount = Json.Document(Web.Contents("https://storfolloikt.pureservice.com/agent/api/user/count",
[
Headers=[
Accept="application/vnd.api+json",
#"X-Authorization-Key"= Text.From(#"PUSapikey")
]
]))[count],
pages = Number.RoundUp(userCount / 500),
pageCount = 0,
Result = {},
totalUserList = List.Generate(
() => [
pageCount = 1,
Result = GetUserData(pageCount)
],
each pageCount <= pages,
each [
pageCount = pageCount + 1,
Result = GetUserData(pageCount)
]
),
#"Converted to Table" = Table.FromList(totalUserList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Column1.Result"}),
#"Column1 Result1" = #"Expanded Column1"{0}[Column1.Result],
#"Converted to Table1" = Table.FromList(#"Column1 Result1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"links", "firstName", "lastName", "middleName", "fullName", "title", "location", "department", "notes", "isAnonymized", "isSuperuser", "role", "notificationScheme", "highlightNotifications", "flushNotifications", "accessKeyNonceId", "pictureId", "emailAddressId", "phonenumberId", "addressId", "companyId", "companyDepartmentId", "companyLocationId", "languageId", "credentialsId", "unavailable", "unavailableChangedById", "unavailableChangedByDate", "disabled", "importUniqueKey", "cf_1", "cf_2", "cf_3", "cf_4", "cf_5", "cf_6", "cf_7", "cf_8", "cf_9", "type", "id", "created", "modified", "createdById", "modifiedById"}, {"links", "firstName", "lastName", "middleName", "fullName", "title", "location", "department", "notes", "isAnonymized", "isSuperuser", "role", "notificationScheme", "highlightNotifications", "flushNotifications", "accessKeyNonceId", "pictureId", "emailAddressId", "phonenumberId", "addressId", "companyId", "companyDepartmentId", "companyLocationId", "languageId", "credentialsId", "unavailable", "unavailableChangedById", "unavailableChangedByDate", "disabled", "importUniqueKey", "cf_1", "cf_2", "cf_3", "cf_4", "cf_5", "cf_6", "cf_7", "cf_8", "cf_9", "type", "id", "created", "modified", "createdById", "modifiedById"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column2",{"links"})
in
#"Removed Columns"Your first steps are correct
let
userCount = Json.Document(Web.Contents("https://storfolloikt.pureservice.com/agent/api/user/count",
[
Headers=[
Accept="application/vnd.api+json",
#"X-Authorization-Key"= Text.From(#"PUSapikey")
]
]))[count],
pages = Number.RoundUp(userCount / 500),
but then you veer off track. Next step is to create a list with all the URLs you need to fetch the data chunks.
since you already know the number of pages you need there is not really a reason to use list.generate any more. A simple
{1..pages}
is enough. Convert that to a table, add your GetUserData function as a custom column, and combine the output.
Brilliant!! Thanks so much for your help! I keep forgetting to use the powerful features provided in the UI.