Safety Culture API Not Pulling all pages

Hi jbrown184 ,

Maybe you can try this way:
Go to Manage Parameters and create parameters for baseUrl, relativePath, and other dynamic parts. And remember to replace the hardcoded values in your M code with these parameters.
Create a custom function in Power Query to fetch paginated data:

let
    fetchData = (relativePath as text, query as record) =>
        let
            baseUrl = "https://api.safetyculture.io",
            authorizationToken = "Bearer 9d433069cc2691f46d2c2a6cbc38331e7db9ee76xxxxxxx",
            response = try Json.Document(Web.Contents(baseUrl, [
                RelativePath = relativePath,
                Query = query,
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ]
            ])) otherwise null
        in
            response
in
    fetchData

Then use this function to fetch and combine all pages:

let
    // Define parameters
    baseUrl = "https://api.safetyculture.io",
    relativePath = "/feed/inspection_items",
    modifiedAfter = "2022-01-01",
    template = "template_895b484e3a48408893666349887a8821",
    archived = "false",
    completed = "true",
    includeInactive = "false",
    limit = "50",

    // Construct the query string
    query = [
        modified_after = modifiedAfter,
        template = template,
        archived = archived,
        completed = completed,
        include_inactive = includeInactive,
        limit = limit
    ],

    // Initial fetch of the first page
    FirstPage = fetchData(relativePath, query),
    Items = if FirstPage <> null then FirstPage[data] else {},
    NextPage = try FirstPage[metadata][next_page] otherwise null,

    // Combine all pages iteratively using pagination
    FetchPages = List.Generate(
        () => [Data = Items, NextPage = NextPage],
        each [NextPage] <> null,
        each [
            Data = try Json.Document(Web.Contents(baseUrl & [NextPage], [
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ]
            ]))[data] otherwise {},
            NextPage = try Json.Document(Web.Contents(baseUrl & [NextPage], [
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ]
            ]))[metadata][next_page] otherwise null
        ],
        each [Data]
    ),

    // Combine all pages data into one list
    allData = List.Combine(FetchPages),
    
    // Convert to table and expand columns
    #"Converted to Table" = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "item_id", "audit_id", "item_index", "template_id", "parent_id", "organisation_id", "created_at", "modified_at", "type", "category", "category_id", "parent_ids", "primeelement_id", "primeelement_index", "label", "response", "response_id", "response_set_id", "is_failed_response", "comment", "media_files", "media_ids", "media_hypertext_reference", "score", "max_score", "score_percentage", "combined_score", "combined_max_score", "combined_score_percentage", "mandatory", "inactive", "location_latitude", "location_longitude"}, {"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.label", "Column1.response", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}),

    // Further transformations
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.id", "Column1.item_id", "Column1.audit_id", "Column1.item_index", "Column1.template_id", "Column1.parent_id", "Column1.organisation_id", "Column1.created_at", "Column1.modified_at", "Column1.type", "Column1.category", "Column1.category_id", "Column1.parent_ids", "Column1.primeelement_id", "Column1.primeelement_index", "Column1.response_id", "Column1.response_set_id", "Column1.is_failed_response", "Column1.comment", "Column1.media_files", "Column1.media_ids", "Column1.media_hypertext_reference", "Column1.score", "Column1.max_score", "Column1.score_percentage", "Column1.combined_score", "Column1.combined_max_score", "Column1.combined_score_percentage", "Column1.mandatory", "Column1.inactive", "Column1.location_latitude", "Column1.location_longitude"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.label", "Question"}, {"Column1.response", "Response"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Please check whether this will work. Thank you!

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.