I’m connecting to the Ruddr API in Power Query for Power BI using the Web.Contents function in M. The Web.Contents function can cause issues in the Power BI Service as the data source might be dynamic and the Power BI Service won’t allow for a scheduled refresh.

From my research, I understood that I would need to properly make use of the Query and RelativePath options to circumnvent this problem. I used these sources: Source 1, [Source 2], and(https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/), Source 3. I rewrote my code to incorporate their recommendations but the Power BI Service still considers it to be a dynamic data source.

What am I doing wrong? I would think the baseUrl is static in my code. See below for my code:

let
    // Retrieve the API key from the parameter
    apiKey = #"ApiKey",
    baseUrl = "https://www.ruddr.io",
    
    // Define the initial query
    initialQuery = [
        limit = "100",
        dateAfter = "2024-01-01",
        startingAfter = ""
    ],

    // Define the relative path
    RelativePath = "/api/workspace/time-entries", 

    // Define the headers, including the API key
    headers = [
        #"Authorization" = "Bearer " & apiKey
    ],

    // Initialize the list to store URLs
    initialUrls = {},

    // Function to fetch a single page of results and record the URL
    fetchPage = (urlFetchPage as text, queryFetchPage as record, relPathFetchPage as text, urls as list) as record =>
        let
            response = Json.Document(Web.Contents(urlFetchPage, [
                Headers = headers,
                Query = queryFetchPage,
                RelativePath = relPathFetchPage
            ])),
            results = response[results],
            hasMore = response[hasMore],
            lastId = if List.Count(results) > 0 then results{List.Count(results) - 1}[id] else null,
            updatedUrls = List.Distinct(urls & {urlFetchPage})
        in
            [Results = results, HasMore = hasMore, LastId = lastId, Urls = updatedUrls],

    // Recursive function to fetch all pages using cursor-based pagination
    fetchAllPages = (urlFetchPages as text, queryFetchPages as record, relPathFetchPages as text, urls as list, results as list) as record =>
        let
            pageResult = fetchPage(urlFetchPages, queryFetchPages, relPathFetchPages, urls),
            pageResults = pageResult[Results],
            newResults = List.Combine({results, pageResults}),
            updatedQuery = if pageResult[HasMore] and pageResult[LastId] <> null then
                Record.TransformFields(queryFetchPages, {{"startingAfter", each pageResult[LastId]}})
            else
                Record.TransformFields(queryFetchPages, {{"startingAfter", each null}}),
            delay = Function.InvokeAfter(() => null, #duration(0, 0, 0, 0.5)), // Delay of 500 milliseconds
            updatedUrls = pageResult[Urls]
        in
            if Record.Field(updatedQuery, "startingAfter") <> null then
                @fetchAllPages(urlFetchPages, updatedQuery, relPathFetchPages, updatedUrls, newResults)
            else
                [Results = newResults, Urls = updatedUrls],

    // Fetch all pages starting from the initial URL
    resultAndUrls = fetchAllPages(baseUrl, initialQuery, RelativePath, initialUrls, {}),
    allResults = resultAndUrls[Results],
    allUrls = resultAndUrls[Urls],

    // Convert the list of results to a table
    resultTable = Table.FromList(allResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Function to ensure unique field names
    EnsureUniqueNames = (fieldNames as list) as list =>
        let
            AddSuffix = (name, existingNames) =>
                let
                    count = List.Count(List.Select(existingNames, each _ = name))
                in
                    if count = 0 then name else name & "_" & Text.From(count + 1),

            uniqueNames = List.Accumulate(fieldNames, {}, (state, current) =>
                state & {AddSuffix(current, state)})
        in
            uniqueNames,

    // Function to recursively expand record columns with unique field names
    ExpandRecordColumns = (table as table, optional parentColumn as text) as table =>
        let
            GetNonNullRecord = (column) =>
                let
                    nonNullValue = List.First(List.Select(Table.Column(table, column), each _ <> null))
                in
                    if Type.Is(Value.Type(nonNullValue), type record) then nonNullValue else null,

            RecordColumns = List.Select(Table.ColumnNames(table), each GetNonNullRecord(_) <> null),
            ExpandTable = List.Accumulate(RecordColumns, table, (state, current) => 
                let
                    record = GetNonNullRecord(current),
                    fieldNames = Record.FieldNames(record),
                    newFieldNames = EnsureUniqueNames(List.Transform(fieldNames, each if parentColumn = null then current & "_" & _ else parentColumn & "_" & current & "_" & _)),
                    expanded = Table.ExpandRecordColumn(state, current, fieldNames, newFieldNames)
                in
                    expanded)
        in
            if List.Count(RecordColumns) = 0 then table else @ExpandRecordColumns(ExpandTable),

    // Function to remove prefix and capitalize column names
    FormatColumnNames = (table as table) as table =>
        let
            removePrefix = (name as text) as text => Text.Replace(name, "Column1_", ""),
            capitalizeWords = (name as text) as text =>
                let
                    words = Text.Split(name, "_"),
                    capitalizedWords = List.Transform(words, each Text.Proper(_)),
                    newName = Text.Combine(capitalizedWords, " ")
                in
                    newName,
            newColumnNames = List.Transform(Table.ColumnNames(table), each capitalizeWords(removePrefix(_))),
            renamedTable = Table.RenameColumns(table, List.Zip({Table.ColumnNames(table), newColumnNames}))
        in
            renamedTable,

    // Expand all records in the table and format column names
    expandedTable = ExpandRecordColumns(resultTable),
    finalTable = FormatColumnNames(expandedTable),
    #"Removed Columns" = Table.RemoveColumns(finalTable,{"Timerstartedat", "Createdat", "Role", "Project Client Name", "Project Id", "Project Name", "Project Client Id", "Timeofftype Id", "Invoice Id", "Invoice Number"}),

    // Combine results and URLs for inspection
    DebugOutput = [
        Results = #"Removed Columns",
        Urls = allUrls
    ],
    // Combine results and URLs for inspection
    Urls = DebugOutput[Urls]
in
    #"Removed Columns"

I rewrote my code to make sure that in the API call everything before the question mark would be in RelativePath and the queries would in the Query parameter. In addition, I put all constants in parameters instead of defining them in the M code.