List Generate function to fetch multiple API pages not working

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