Changing from a dynamic to a fixed data source

[JJS8080]

I have written the below code to pull data from a api, I want to be able to refresh it automatically daily however due to it being a dynamic data source I cannot how can I change the code to get around this.

let

    api_id = "a",

    api_token = "b",

    encodedAuth = "Basic " & Binary.ToText(Text.ToBinary(api_id & ":" & api_token), BinaryEncoding.Base64),

    // Recursive paging function (your original code)

    GetAllPages = (url as text, acc as list) =>

        let

            Source = Json.Document(Web.Contents(url, [

                Headers = [

                    Authorization = encodedAuth,

                    Accept = "application/json"

                ]

            ])),

            Data = Source[calls],

            Meta = Source[meta],

            NextLink = try Meta[next_page_link] otherwise null,

            NewAcc = List.Combine({acc, Data})

        in

            if NextLink <> null and NextLink <> "" then

                @GetAllPages(NextLink, NewAcc)

            else

                NewAcc,

    // --- New: create fortnightly windows ---

    StartDate = #datetime(2025, 4, 1, 0, 0, 0),

    EndDate = DateTime.From(DateTimeZone.LocalNow()),

    FortnightDays = 14,

    NumFortnights = Number.RoundUp(Duration.Days(EndDate - StartDate) / FortnightDays),

    // List of start dates for each fortnight

    FortnightStarts = List.Transform({0..NumFortnights-1}, each StartDate + #duration(_ * FortnightDays,0,0,0)),

    // Function to fetch calls for a single fortnight

    FetchFortnight = (fortStart as datetime) =>

        let

            FortEnd = if fortStart + #duration(FortnightDays,0,0,0) > EndDate then EndDate else fortStart + #duration(FortnightDays,0,0,0),

            FromUnix = Number.ToText(Duration.TotalSeconds(fortStart - #datetime(1970,1,1,0,0,0))),

            ToUnix = Number.ToText(Duration.TotalSeconds(FortEnd - #datetime(1970,1,1,0,0,0))),

            url = "https://api.aircall.io/v1/calls?per_page=50&page=1&from=" & FromUnix & "&to=" & ToUnix,

            Calls = GetAllPages(url, {})

        in

            Calls,

    // Fetch all calls fortnight by fortnight and combine

    AllCalls = List.Combine(List.Transform(FortnightStarts, each FetchFortnight(_))),

    // Convert to table properly (from list of records)

    Output = Table.FromRecords(AllCalls)

[v-echaithra]

Hi @JJS8080 ,

Thank you @danextian , @Ahmed-Elfeel  for your inputs.

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 
Chaithra E.

[JJS8080]

Hey, tried both methods but didnt get the result I wanted as still ended up being a dynamic data source. 

[v-echaithra · Accepted solution]

Hi @JJS8080 ,

If you're still seeing a dynamic data source even after trying the previous suggestions, that means Power BI is detecting a fully constructed URL somewhere in the query. Power BI will always block scheduled refresh when any Web.Contents call uses a dynamically-built URL string.
Try to rewrite your paging function so that every Web.Contents call uses this structure:

Web.Contents(
"https://api.aircall.io",
[
RelativePath = "v1/calls",
Query = [
per_page = "50",
page = PageNumber,
from = FromUnix,
to = ToUnix
],
Headers = [ Authorization = encodedAuth ]
])

No concatenated strings.
No full URLs.
No manually built "https://api.aircall.io/v1/calls?...“.
Once the paging logic is rewritten using RelativePath + Query only, your dataset will become refreshable in the Power BI Service.

Hope this helps.
Thank you.

[JJS8080]

Thanks for the help, Issue is now fixed

[danextian]

Hi @JJS8080 

Power BI Service doesn't like this line of code:

  url = "https://api.aircall.io/v1/calls?per_page=50&page=1&from=" & FromUnix & "&to=" & ToUnix,

Instead of directly using a concatenated url, use relative path

// Base URL (domain only)
BaseUrl = "https://api.aircall.io",

// Function to fetch calls for a single fortnight
FetchFortnight = (fortStart as datetime) =>
    let
        FortEnd =
            if fortStart + #duration(FortnightDays,0,0,0) > EndDate
            then EndDate
            else fortStart + #duration(FortnightDays,0,0,0),

        FromUnix = Number.ToText(Duration.TotalSeconds(fortStart - #datetime(1970,1,1,0,0,0))),
        ToUnix   = Number.ToText(Duration.TotalSeconds(FortEnd   - #datetime(1970,1,1,0,0,0))),

        // relative path only
        _RelativePath =
            "/v1/calls?per_page=50&page=1&from=" & FromUnix & "&to=" & ToUnix,

        // Use Web.Contents with relative path
        Calls = Web.Contents(
            BaseUrl,
            [
                RelativePath = _RelativePath,
                Headers = [
                    #"Accept" = "application/json"
                ]
            ]
        )
    in
        Calls