Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Incremental Refresh of Custom Queries

hello everyone, i made api codes in powerquery to gather data from website, when i publish to service, i cant incremental refresh becaue it says 
"You can't schedule refresh for this semantic model because the following data sources currently don't support refresh:

Data source for Query1"

and then i have this

"this dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

  • Data source for Query1"



what can i do?

here is my query

let
    // Data Required
    organizationId = "ID",
    accessToken = "Access Token",
    TodayDateTime = DateTime.LocalNow(),
    TodayDate = DateTime.Date(TodayDateTime),
    TwoYearAgo = Date.AddYears(TodayDate, -2),
    FormattedDate = Date.ToText(TwoYearAgo, "yyyyMMdd") & "000000",
    Credentials = Text.ToBinary(organizationId & ":" & accessToken),
    EncodedCredentials = "Basic " & Binary.ToText(Credentials, BinaryEncoding.Base64),

    // Fetch the first page to get total number of records
    URL = "https://app.na3.teamsupport.com/api/json/Tickets",
    RecordsParams = [
        IsClosed = "True",
        DateClosed = FormattedDate,
        pageSize = "1",
        pageNumber = "1"
    ],
    InitialResponse = Web.Contents(URL, [Query = RecordsParams, Headers = [Authorization = EncodedCredentials]]),
    RecordsContent = Json.Document(InitialResponse),

    // Return the number of pages required
    TotalRecords = RecordsContent[TotalRecords],
    RecordsperPage = 1000,
    Totalpages = Number.RoundUp(Number.FromText(TotalRecords)/RecordsperPage,0),

    // Get Data per page
    PageNumbers = List.Numbers(1, Totalpages),
    Pages = List.Transform(PageNumbers, each Function.InvokeAfter(()=>
        let
            Params = [
                IsClosed = "True",
                DateClosed = FormattedDate,
                pageSize = Number.ToText(RecordsperPage),
                pageNumber = Number.ToText(_)
            ],
            DataResponse = Web.Contents(URL, [Query = Params, Headers = [Authorization = EncodedCredentials]]),
            DataContent = Json.Document(DataResponse),
            FullData = DataContent[Tickets]
        in
            FullData,
    #duration(0,0,0,0))),
    
    // Combine and Clean data from all pages
    CombineData = List.Combine(Pages),// Combine data from all pages
    CleanData =  List.Transform(CombineData, each Record.SelectFields(_, {"GroupName", "Name", "ProductName", "TicketTypeName", "Status", "UserName", "Severity", "TicketNumber", "DateClosed", "DaysClosed", "CloserName", "HoursSpent", "PrimaryCustomer"}, MissingField.Ignore)),

    // Convert to table
    OutputTable = Table.FromRecords(CleanData),
    #"Replaced Value" = Table.ReplaceValue(OutputTable,null,"",Replacer.ReplaceValue,{"GroupName", "Name", "ProductName", "TicketTypeName", "Status", "UserName", "Severity", "TicketNumber", "DateClosed", "DaysClosed", "CloserName", "HoursSpent", "PrimaryCustomer"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"GroupName", type text}, {"Name", type text}, {"ProductName", type text}, {"TicketTypeName", type text}, {"Status", type text}, {"UserName", type text}, {"Severity", type text}, {"TicketNumber", Int64.Type}, {"DateClosed", type datetime}, {"DaysClosed", Int64.Type}, {"CloserName", type text}, {"HoursSpent", Int64.Type}, {"PrimaryCustomer", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Company", each "Vocantas", type text)
in
    #"Added Custom"

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    nope i fixed the issue by addid relative path now it works

2 Replies

  • - this has nothing to do with incremental refresh

    - why are you pulling the first page twice?

    - your issue might be caused by the forced attempt at serialization.  Try without the Function.InvokeAfter  hack.

    • Anonymous's avatar
      Anonymous
      Not applicable

      nope i fixed the issue by addid relative path now it works