PowerBI Integration with Custom Query

let
    // Define the base URL and relative path
    BaseUrl = "https://tis.obss.io",
    RelativePath = "/rest/list2",
     
    // Change this with your Timepiece JWT
    TP_JWT = "<tis_jwt>",
    PageSize = 1000,
 
    // Define a function to retrieve a single page of data
    GetPage = (nextPageToken as nullable text) =>
        let
            // Base query parameters
            query = [
                filterType = "customJQL",
                customJQL = "project not in (HTP, TTP) or key < TTP-101",
                columnsBy = "statusDuration",
                fields = "status, assignee",
                statuses = "",
                calendar = "normalHours",
                viewFormat = "days",
                outputType = "jsonSimplified", 
                emptyValueToken = "nochar",
                pageSize = Text.From(PageSize)
            ],

            // Conditionally add nextPageToken to the query for subsequent requests
            finalQuery = if nextPageToken <> null then Record.Combine({query, [nextPageToken = nextPageToken]}) else query,
            
            // Fetch the JSON data from the API
            Source = Web.Contents(BaseUrl & RelativePath, [
                Query = finalQuery,
                Headers = [Authorization = "TISJWT " & TP_JWT]
            ]),

            // Parse the JSON content
            JsonData = Json.Document(Source)
        in
            JsonData,

    // Generate a list containing the JSON response for each page
    AllPages = List.Generate(
        () => GetPage(null), // Initial call: No token is sent for the first page.
        each _ <> null, // Condition: Continue as long as the previous step returned a valid response.
        each if [nextPageToken]? <> null then GetPage([nextPageToken]) else null // Next step: Use the token from the previous response to get the next page. Stop if no token is found.
    ),
    
    // Extract the 'results' list from each page's JSON response.
    ResultsLists = List.Transform(AllPages, each if Record.HasFields(_, "results") then _[results] else {}),

    // Combine all the individual 'results' lists into one large list of records
    AllRecords = List.Combine(ResultsLists),

    // 1. Get a distinct list of all possible column names from all records
    AllColumnHeaders = List.Distinct(List.Combine(List.Transform(AllRecords, each Record.FieldNames(_)))),
    
    // 2. Create a default record that has all possible columns set to "-"
    DefaultRecord = Record.FromList(List.Repeat({"-"}, List.Count(AllColumnHeaders)), AllColumnHeaders),

    // 3. Ensure every record has all columns by merging it with the default record.
    // This adds any missing fields (like "Open") with "-".
    NormalizedRecords = List.Transform(AllRecords, each Record.Combine({DefaultRecord, _})),

    // Convert the normalized (and now structurally identical) records into a single table
    AllData = Table.FromRecords(NormalizedRecords)
in
    AllData