List Generate function to fetch multiple API pages not working

[danielru]

Hi,

I have this function- the aim of which is to fetch multi-page results from an api:

let
    // Initial CandidateIDs List from Offers Query
    CandidateIDsList = offers[candidate_id],
    
    // ResultsPerPage
    ResultsPerPage = 50,

    // Generate the List of Results
    AllCandidates = List.Generate(
        () => 
            let
                InitialPage = 1,
                CandidateBatch = CandidatesIDS(InitialPage,CandidateIDsList, ResultsPerPage),
                ApiResponse = try #"FNGet-candidates"(
                    "candidates", 
                    CandidateBatch, 
                    Text.From(ResultsPerPage), 
                    InitialPage
                ) 
                otherwise null
            in
                [
                    Page = InitialPage,
                    NextPage = null,
                    Result = ApiResponse
                ],
        each not List.IsEmpty([Result]),
        each 
            let
                NextPage = if [NextPage] = null then [Page]+1 else [NextPage] + 1,
                CandidateBatch = CandidatesIDS(NextPage-1,CandidateIDsList, ResultsPerPage),
                ApiResponse = try #"FNGet-candidates"(
                    "candidates", 
                    CandidateBatch, 
                    Text.From(ResultsPerPage), 
                    NextPage
                )  
                otherwise null
            in
                [
                Result = ApiResponse
                ],
        each [Result]
)

There are 2 other functions called in the above function:

1. CandidatesIDS- this function creates a CSV string from a list of Candidate IDs and returns it as a value that is in turn used as a param that is passed to the api query (see third query below- #"FNGet-candidates"):

= (pageNo as number,candidateIDs as list,resultsPerPage as number)=>
let  
startIndex = (pageNo -1) * resultsPerPage,
Batch = List.Range(candidateIDs,startIndex,resultsPerPage),
candidateIDsText =List.Transform(Batch, each Text.From(_)),
candidateIDString = Text.Combine(candidateIDsText,",")

in
    candidateIDString

 output of this is page N, 50 CSV results per string.

 Sample output "1235,12658,35489..."

The final query that does the api call

#"FNGet-candidates":

= (relPath as text,candidateIDs as text,perPg as text, page as number)=>
let
baseURL= "https://...io/v1",
headers = [#"Content-Type" = "application/json"],

    response = Web.Contents(baseURL,[
            RelativePath = relPath,
            Query = [
                candidate_ids= candidateIDs,
                per_page= perPg,
                page= Number.ToText(page)
                    ]
        ]),
    jsonResponse = Json.Document(response)
    in
    jsonResponse

The final result returned by the top function 'AllCandidates' is 

The problem is that there should be 9 lists in the list, not just one. This means that the first query is working but not the second, third, fourth, etc.

I've done many tests to invoke the functions manually. They all work separately with hard coded inputs for the Page and NextPage variables.

These variables do work in a twin list generate function test I did below:

let
    Source = List.Generate(()=>
  [
     Result = try #"CandidatesIDS-List"(1,offers[candidate_id],50) otherwise null, Page =1
  ],
  each not List.IsEmpty([Result]),
  each [
       Result = try #"CandidatesIDS-List"([Page],offers[candidate_id],50) otherwise null, Page =[Page]+1
  ],
  each [Result]  
),

which returned the correct number of api pages expected namely 9:

However, I'm thinking that since I'm passing a function into another function as a parameter and inside that function I'm passing in the NextPage, Page etc variables, there is a scope chain issue and these page variables are undefined resulting in the loop closing after the first call.

I'm been struggling with this for days. Please can someone help me understand where I'm going wrong?

Thank you so much!

Regards,

Daniel

[lbendlin · Accepted solution]

Pagination is usually used for results.  You seem to be attempting to do pagination for the source criteria ( 50 candidate IDs per call) .  Make the List.Generate return strings, then add a column that calls the API with the string, then combine the results. 

[danielru]

Hi @lbendlin,

Thank you for your help before. I have another issue which I have solved until now by changing the privacy settings but I need to overcome the firewall error that I'm getting and need to rebuild the query.

Formula.Firewall: Query 'candidates' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

let
    Source = List.Generate(()=>
  [
     Result = try #"CandidatesIDS-List"(1,offers[candidate_id],50) otherwise null, Page =1
  ],
  each not List.IsEmpty([Result]),
  each [
       Result = try #"CandidatesIDS-List"([Page]+1,offers[candidate_id],50) otherwise null, Page =[Page]+1
  ],
  each [Result]  
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values",{{"Column1", "CSVCandidateIDs"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Candidates", each #"FNGet-candidates"("candidates", [CSVCandidateIDs])),
    #"Expanded Candidates" = Table.ExpandListColumn(#"Invoked Custom Function", "Candidates"),
    #"Expanded Candidates1" = Table.ExpandRecordColumn(#"Expanded Candidates", "Candidates", {"id", "first_name", "last_name", "title", "recruiter", "keyed_custom_fields"}, {"id", "first_name", "last_name", "title", "recruiter", "keyed_custom_fields"}),
    #"Expanded recruiter" = Table.ExpandRecordColumn(#"Expanded Candidates1", "recruiter", {"name"}, {"name"}),
    #"Expanded keyed_custom_fields" = Table.ExpandRecordColumn(#"Expanded recruiter", "keyed_custom_fields", {"employee_id__hris_"}, {"employee_id__hris_"}),
    #"Expanded employee_id__hris_" = Table.ExpandRecordColumn(#"Expanded keyed_custom_fields", "employee_id__hris_", {"value"}, {"value"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded employee_id__hris_",{"first_name", "last_name"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"CandidateName"),
    #"Renamed Columns1" = Table.RenameColumns(#"Merged Columns",{{"name", "Recruiter"}, {"value", "Employee_id__hris_"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"id", "CandidateName", "Recruiter", "Employee_id__hris_"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Other Columns",{{"CandidateName", "candidate_Name"}, {"Recruiter", "recruiter_Name"}, {"Employee_id__hris_", "employee_id__hris_"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns2", {"id"})
in
    #"Removed Duplicates"

Where:

#"FNGet-candidates":

= (relPath as text,candidateIDs as text,perPg as text, page as number)=>
let
baseURL= "https://...io/v1",
headers = [#"Content-Type" = "application/json"],

    response = Web.Contents(baseURL,[
            RelativePath = relPath,
            Query = [
                candidate_ids= candidateIDs,
                per_page= perPg,
                page= Number.ToText(page)
                    ]
        ]),
    jsonResponse = Json.Document(response)
    in
    jsonResponse
 

I've checked various sources and videos online for guidance but in their use case they are only rebuilding for a the query for a single data point (the file path) whereas I'm running a function in a new column that's referencing another column in the table as a param for the function.

I'm not sure how to rebuild this query. Do you have any pointers?

Thank you again.

Regards,

Daniel

[lbendlin]

Inline the 

#"FNGet-candidates"

function so that it is housed inside your query, same like the (unfortunately named) "Source"  function at the top of your code.

[danielru]

Hi @lbendlin ,

Firstly thank you so much for getting back to me.

I'm trying to implement this. I'm running into some obstacles but I'll continue a bit further then will update you.

Thank you again.

Regards,

Daniel

[danielru]

Worked! Thank you very much for you expert advice!!!

[danielru]

Hi @lbendlin,

Thanks for responding so quickly.

Interesting! Thank you for your input and I will give it a try. I'll let you know how it goes.

Regards,

Daniel