Forum Discussion
Struggling with the cyclic reference error
I have coded the following function, which recursively calls itself in order to retrieve the full list of users from a REST API:
let
GetUserData = (startFrom as number, uCount as number, result) =>
let
userCount = if uCount <> null then
uCount
else
Json.Document(Web.Contents("https://storfolloikt.pureservice.com/agent/api/user/count",
[Headers=[Accept="application/vnd.api+json",#"X-Authorization-Key"= Text.From(#"PUSapikey")]])),
userResult = Json.Document(Web.Contents("https://storfolloikt.pureservice.com/agent/api/user/?From=" & Text.From(startFrom),
[Headers=[Accept="application/vnd.api+json",#"X-Authorization-Key"= Text.From(#"PUSapikey")]])),
// See if more need to be read
combinedResult = List.Distinct(List.Combine(userResult, result)),
startFrom = if userCount < List.Count(combinedResult) then List.Count(combinedResult) else null,
#"Converted to Table" = Record.ToTable(combinedResult),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"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"}, {"Value.links", "Value.firstName", "Value.lastName", "Value.middleName", "Value.fullName", "Value.title", "Value.location", "Value.department", "Value.notes", "Value.isAnonymized", "Value.isSuperuser", "Value.role", "Value.notificationScheme", "Value.highlightNotifications", "Value.flushNotifications", "Value.accessKeyNonceId", "Value.pictureId", "Value.emailAddressId", "Value.phonenumberId", "Value.addressId", "Value.companyId", "Value.companyDepartmentId", "Value.companyLocationId", "Value.languageId", "Value.credentialsId", "Value.unavailable", "Value.unavailableChangedById", "Value.unavailableChangedByDate", "Value.disabled", "Value.importUniqueKey", "Value.cf_1", "Value.cf_2", "Value.cf_3", "Value.cf_4", "Value.cf_5", "Value.cf_6", "Value.cf_7", "Value.cf_8", "Value.cf_9", "Value.type", "Value.id", "Value.created", "Value.modified", "Value.createdById", "Value.modifiedById"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"})
in
if startFrom <> null then
@GetUserData(startFrom, userCount, combinedResult)
else
#"Removed Columns"
in
GetUserData
When I try to call the function, or invoke it from Power Query, I get the cyclic reference error.
Could one of you PQ masters out there help me tackle this issue?
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.
5 Replies
- don't use recursive functions for this. Use List.Generate instead.
- try not to do any transforms until after you combined the data.
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.