Hello all!
I am having trouble trying to configure the scheduled refresh in Power BI because my data source is dynamic.
My query fetches ticket data from the Movidesk API. The API is paginated, and I use the List.Generate() to paginate through the $top and $skip functions from the API. I believe this is what makes the API url dynamic.
Can anyone help me on how to fix it so that I can use the scheduled refresh?
Thanks
let
// Base URL for the API call
BaseUrl = "https://api.movidesk.com",
// Function to fetch data using Web.Contents
FetchData = (baseUrl as text, skip as number) as nullable list =>
let
// Construct the relative path and query string
RelativePath = "/public/v1/tickets/past",
QueryString = [
token="secret-token",
#"$select"="id,isDeleted,createdDate,type,category,urgency,status,baseStatus,origin,owner,ownerteam,createdBy,tags,resolvedIn,reopenedIn,closedIn,lifetimeWorkingTime,stoppedTimeWorkingTime,lastUpdate,serviceFull,serviceFirstLevel,serviceSecondLevel,serviceThirdLevel,protocol,slaAgreement,slaAgreementRule,slaSolutionTime,slaResponseTime,slaSolutionChangedByUser,slaSolutionChangedBy,slaSolutionDate,slaSolutionDateIsPaused,slaResponseDate,slaRealResponseDate,type,originEmailAccount,createdBy,contactForm,actionCount,resolvedInFirstCall",
#"$orderby"="createdDate asc",
#"$expand"="owner,clients($select=id,businessName,email,personType,profileType),childrenTickets($select=id,isDeleted),parentTickets($select=id,isDeleted),assets($select=name,label,categoryFull,categoryFirstLevel,categorySecondLevel,categoryThirdLevel)",
#"$filter"="createdDate gt 2016-10-01",
#"$top"="100",
#"$skip"=Number.ToText(skip)
],
Source = Web.Contents(baseUrl, [RelativePath=RelativePath, Query=QueryString]),
Data = try Json.Document(Source) otherwise null
in
Data,
// Generate a list of data using List.Generate
Consulta2 = List.Generate(
() => [result= FetchData(BaseUrl, 0), skip = 0],
each [result] <> null and List.Count([result]) > 0,
each [result= FetchData(BaseUrl, [skip] + 100), skip =[skip] + 100],
each [result]
),
// rest of the data modelling
