Forum Discussion
Safety Culture API Not Pulling all pages
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"
7 Replies
() => [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?
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"It is only pull 50 rows.
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 maybe that 50 is another limitation of your API? Do you need to specify a rows per page number ?
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"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 fetchDataThen 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.
