This dataset includes a dynamic data source (RelativePath is set up)

[jbrown184]

Hey I have the following query.  RelativePath is set up but it is still giving me the This dataset includes a dynamic data source error. Please help!

let
    // Define parameters
    baseUrl = "https://api.safetyculture.io",
    relativePath = "/feed/inspection_items",
    authorizationToken = "Bearer 9d433069cc2691f46d2c2a6cbc38331e7db9eexxxxx",
    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 ,

Thanks for lbendlin's reply!
And @jbrown184 , I've replied to you at the bottom of this thread, please check if my solution works, thanks!
Safety Culture API Not Pulling all pages - Microsoft Fabric Community

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.

[lbendlin]

Data = try Json.Document(Web.Contents(baseUrl & [NextPage]

This needs to go into RelativePath too.

[jbrown184]

When I plug in the following it removes the Pagination.

[
                RelativePath = relativePath,
                Query = query,
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ]

[lbendlin]

    FetchPages = List.Generate(
        () => [Data = Items, NextPage = NextPage],
        each [NextPage] <> null,
        each [
            PG = Json.Document(Web.Contents(baseUrl , [
                Headers = [
                    #"Accept" = "application/json",
                    #"Authorization" = authorizationToken
                ],RelativePath = [NextPage]
            ])),
            Data = try PG[data] otherwise {},
            NextPage = try PG[next_page] otherwise null
        ],
        each [Data]
    ),

[jbrown184]

Okay when I do that it only gives me one day with 50 rows. Here is the original code:

    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]
    ),

[lbendlin]

As I said you cannot do this

Json.Document(Web.Contents(baseUrl & [NextPage]