Struggling with the cyclic reference error

[eddiehartman]

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?

[eddiehartman]

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"

[lbendlin · Accepted solution]

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.

[eddiehartman]

Brilliant!! Thanks so much for your help! I keep forgetting to use the powerful features provided in the UI. 

[eddiehartman]

That looks promising. Thanks for the tip! However, I have seen a lot of examples with recursion. How do you get this to work without triggering a cyclic reference error?

[lbendlin]

- don't use recursive functions for this. Use List.Generate instead.

- try not to do any transforms until after you combined the data.