Forum Discussion
Changing from a dynamic to a fixed data source
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)
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.
5 Replies
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 CallsHi 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.Hey, tried both methods but didnt get the result I wanted as still ended up being a dynamic data source.
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.
