Safety Culture API Not Pulling all pages

[jbrown184]

Hey,

I have this code and it is not pulling up all the pages. The Relitive path is set up correctly and tested. Below is the next_page token documentation from the API Website.

SafetyCulture API next_page token

The next_page path MUST be used to fetch the next page of data. Do not construct this yourself as it's subject to change and may result in your exports failing in the future.

If next_page returns null, that means there are no more records to fetch.

See the following API usage example:

JavaScript

async fetchInspections() {
  // Only fetch inspections that have been updated
  const lastModifiedAt = await store.getLastModifiedAt('inspections');

  let path = `/feed/inspections?modified_after=${lastModifiedAt.toISOString()}`

  while(url) {
    const response = await get(`https://api.safetyculture.io${path}`);
    await store.saveRows(response.data);

    // next_page will be null when there is no more data to be fetched
    path = response.metadata.next_page;
  }
}

M Code (Help Needed)

let
// Define parameters
baseUrl = "https://api.safetyculture.io",
relativePath = "/feed/inspection_items",
authorizationToken = "Bearer API Token",
modifiedAfter = "2024-10-27T23:14:23.000Z",
template = "template_895b484e3a48408893666349887a8821",
archived = "false",
completed = "true",
includeInactive = "false",

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

// Use Web.Contents function
fetchData = (relativePath as text, query as record) =>
let
response = try Json.Document(Web.Contents(baseUrl, [
RelativePath = relativePath,
Query = query,
Headers = [
#"accept" = "application/json",
#"authorization" = authorizationToken
]
])) otherwise null,
data = if response <> null and Record.HasFields(response, "data") then response[data] else {},
nextPage = if response <> null and Record.HasFields(response, "metadata") and Record.HasFields(response[metadata], "next_page") then response[metadata][next_page] else null
in
[data = data, nextPage = nextPage],

firstPage = try fetchData(relativePath, query) otherwise [data = {}, nextPage = null],

allData = List.Generate(
() => [data = firstPage[data], nextPage = firstPage[nextPage]],
each List.Count([data]) > 0 and [nextPage] <> null,
each let
nextPageData = if [nextPage] <> null then fetchData(Text.Middle([nextPage], Text.Length(baseUrl)), query) else [data = {}, nextPage = null]
in
nextPageData,
each [data]
),

combinedData = List.Combine(allData),
#"Converted to Table" = Table.FromList(combinedData, 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"}),
#"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"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Question] <> "Title Page")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
#"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
#"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Question]), "Question", "Response"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "if response is |Yes / Sí|"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Conducted on / Fecha.1", type date}, {"Conducted on / Fecha.2", type time}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"Conducted on / Fecha.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"Conducted on / Fecha.1", type date}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Conducted on / Fecha.1", "Date"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Date", "Date - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Date - Copy", "Date - Copy - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "Week"}, {"Date - Copy - Copy", "Year"}}),
#"Calculated Week of Year" = Table.TransformColumns(#"Renamed Columns2",{{"Week", Date.WeekOfYear, Int64.Type}}),
#"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}})
in
#"Extracted Year"

[jbrown184]

Hey I have set up the RelativePath and Query but it is still saying the following on power bi online:

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: 

• Data source for Query1

I am able to pull all pages with the next_page token.

Here is the M Code:

let
    // Define parameters
    baseUrl = "https://api.safetyculture.io",
    relativePath = "/feed/inspection_items",
    authorizationToken = "Bearer 9d433069cc2691f46d2c2a6cbc38331e7db9ee76xxxxxxx",
    modifiedAfter = startDate,  // Make sure startDate is defined
    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
    ],

    // Function to fetch data
    fetchData = (relativePath as text, query as record) =>
        let
            response = try Json.Document(Web.Contents(baseUrl, [
                RelativePath = relativePath,
                Query = query,
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ]
            ])) otherwise null
        in
            response,

    // 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"}),

    // Optionally remove or rename columns
    #"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"}),

    // Further transformations (example: pivoting, renaming, filtering)
    #"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"

[Anonymous]

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.

[jbrown184]

This is how many records I should have

Row Labels	Count of Date
10/31/2024	82
11/1/2024	137
11/4/2024	105
11/5/2024	129
11/6/2024	89
11/7/2024	90
11/8/2024	10
11/11/2024	81
11/12/2024	76
11/13/2024	115
11/14/2024	96
11/15/2024	3
11/18/2024	82
11/19/2024	120
11/20/2024	91
11/21/2024	98
11/22/2024	105
11/25/2024	99
11/26/2024	108
11/27/2024	97
11/28/2024	28
11/29/2024	98
12/2/2024	58
12/3/2024	60
12/4/2024	101
12/5/2024	108
12/6/2024	8
12/8/2024	2
12/9/2024	157
12/10/2024	117
Grand Total	2550

[lbendlin]

maybe that 50 is another limitation of your API?  Do you need to specify a rows per page number ?

[lbendlin]

() => [data = firstPage[data], nextPage = firstPage[nextPage]],

This will fetch the first page twice. Not a big issue but worth mentioning.

How do you know that not all pages are fetched?

[jbrown184]

It is only pull 50 rows.

[jbrown184]

It is only pulling 10/31/2024.

This one pulls correctly but doesn't have a reletivepath

let
    // Define parameters
    BaseUrl = "https://api.safetyculture.io/feed/inspection_items",
    QueryParams = [
        template = "template_895b484e3a48408893666349887a8821",
        archived = "false",
        completed = "true",
        include_inactive = "true",
        modified_after = "2024-10-28T23:14:23.000Z"
    ],
    AuthorizationToken = "Bearer 9d433069cc2691f46d2c2axxxxxx",
    
    // Fetch the first page of data
    FirstPageUrl = BaseUrl & "?" & Uri.BuildQueryString(QueryParams),
    FirstPage = try Json.Document(Web.Contents(FirstPageUrl, [Headers=[Accept="application/json", Authorization=AuthorizationToken]])) otherwise null,
    
    // If the first page fails, return null
    Items = if FirstPage <> null then FirstPage[data] else {},
    NextPage = try FirstPage[metadata][next_page] otherwise null,
    
    // Combine all pages iteratively
    FetchPages = List.Generate(
        () => [CurrentUrl = FirstPageUrl, Data = Items, NextPage = NextPage],
        each [NextPage] <> null,
        each [
            CurrentUrl = "https://api.safetyculture.io" & [NextPage],
            PageData = try Json.Document(Web.Contents("https://api.safetyculture.io" & [NextPage], [Headers=[Accept="application/json", Authorization=AuthorizationToken]])) otherwise null,
            Data = if PageData <> null then PageData[data] else {},
            NextPage = try PageData[metadata][next_page] otherwise null
        ],
        each [Data]
    ),
    AllItems = List.Combine(FetchPages),
    
    // Convert the list to a table
    #"Converted to Table" = Table.FromList(AllItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "item_id", "audit_id", "template_id", "label", "response"}),

    // Perform transformations (rename, filter, add columns, etc.)
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1", {"id", "item_id", "audit_id", "template_id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"label", "Question"}, {"response", "Response"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Question] <> "Title Page"),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows", {{"Response", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),

    // Remove unnecessary columns
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    
    // Pivot the data based on the 'Question' field
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Question]), "Question", "Response"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    
    // Duplicate and reorder date columns
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns2", "Conducted on / Fecha", "Conducted on / Fecha - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Conducted on / Fecha - Copy", "Conducted on / Fecha - Copy - Copy"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column1",{"Conducted on / Fecha", "Conducted on / Fecha - Copy - Copy", "Conducted on / Fecha - Copy", "How many items were in the tote? (¿Cuántos artículos había en el tote?)", "Was there an error? (¿Hubo un error?)", "if response is |Yes / Sí|", "LP Number", "User I.D / Nombre de usuario", "What was the issue? (¿Cuál fue el problema?)", "What type of apparel had the error? (¿Qué tipo de prenda tuvo el error?)", "Photo of Error / Foto de error"}),
    
    // Split date columns by delimiter 'T'
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2", "Conducted on / Fecha.3"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Split Column by Delimiter",{"Conducted on / Fecha.2", "Conducted on / Fecha.3"}),
    
    // More splits for duplicated columns
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "Conducted on / Fecha - Copy - Copy", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha - Copy - Copy.1", "Conducted on / Fecha - Copy - Copy.2", "Conducted on / Fecha - Copy - Copy.3"}),
    #"Removed Columns4" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Conducted on / Fecha - Copy - Copy.2", "Conducted on / Fecha - Copy - Copy.3"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns4", "Conducted on / Fecha - Copy", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha - Copy.1", "Conducted on / Fecha - Copy.2"}),
    #"Removed Columns5" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Conducted on / Fecha - Copy.2"}),
    
    // Renaming columns for clarity
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns5",{{"Conducted on / Fecha - Copy - Copy.1", "Week"}, {"Conducted on / Fecha - Copy.1", "Year"}, {"Conducted on / Fecha.1", "Date"}}),
    
    // Transform data types correctly
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}, {"Week", type date}, {"Year", type date}}),
    #"Calculated Week of Year" = Table.TransformColumns(#"Changed Type1",{{"Week", Date.WeekOfYear, Int64.Type}}),
    #"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}}),
    
    // Convert quantity to integer
    #"Changed Type2" = Table.TransformColumnTypes(#"Extracted Year",{{"How many items were in the tote? (¿Cuántos artículos había en el tote?)", Int64.Type}})
in
    #"Changed Type2"

This one only shows 50 rows and one day and does have reletivepath

let
    // Define parameters
    baseUrl = "https://api.safetyculture.io",
    relativePath = "/feed/inspection_items",
    authorizationToken = "Bearer 9d433069cc2691f46d2c2a6cbcxxxxxxxx",
    modifiedAfter = "2024-10-27T23:14:23.000Z",
    template = "template_895b484e3a48408893666349887a8821",
    archived = "false",
    completed = "true",
    includeInactive = "false",

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

    // Use Web.Contents function
    fetchData = (relativePath as text, query as record) =>
let
    response = try Json.Document(Web.Contents(baseUrl, [
        RelativePath = relativePath,
        Query = query,
        Headers = [
            #"accept" = "application/json",
            #"authorization" = authorizationToken
        ]
    ])) otherwise null,
    data = if response <> null and Record.HasFields(response, "data") then response[data] else {},
    nextPage = if response <> null and Record.HasFields(response, "metadata") and Record.HasFields(response[metadata], "next_page") then response[metadata][next_page] else null
in
    [data = data, nextPage = nextPage],

firstPage = try fetchData(relativePath, query) otherwise [data = {}, nextPage = null],

allData = List.Generate(
    () => [data = firstPage[data], nextPage = firstPage[nextPage]],
    each List.Count([data]) > 0 and [nextPage] <> null,
    each let
        nextPageData = if [nextPage] <> null then fetchData(Text.Middle([nextPage], Text.Length(baseUrl)), query) else [data = {}, nextPage = null]
    in
        nextPageData,
    each [data]
),

combinedData = List.Combine(allData),
#"Converted to Table" = Table.FromList(combinedData, 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"}),
    #"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"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Question] <> "Title Page")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([Question], "Fecha") then [Index] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns1",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Question]), "Question", "Response"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom", "if response is |Yes / Sí|"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Conducted on / Fecha", Splitter.SplitTextByDelimiter("T", QuoteStyle.Csv), {"Conducted on / Fecha.1", "Conducted on / Fecha.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Conducted on / Fecha.1", type date}, {"Conducted on / Fecha.2", type time}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"Conducted on / Fecha.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns3",{{"Conducted on / Fecha.1", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Conducted on / Fecha.1", "Date"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns1", "Date", "Date - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Date - Copy", "Date - Copy - Copy"),
    #"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column1",{{"Date - Copy", "Week"}, {"Date - Copy - Copy", "Year"}}),
    #"Calculated Week of Year" = Table.TransformColumns(#"Renamed Columns2",{{"Week", Date.WeekOfYear, Int64.Type}}),
    #"Extracted Year" = Table.TransformColumns(#"Calculated Week of Year",{{"Year", Date.Year, Int64.Type}})
in
    #"Extracted Year"