Enable OData to process records in batches into PowerBI dashboard

Process

To enable OData to process records in batches into the power bi dashboard or excel requires:

1. A Power Query function which pages the selected table
2. A query for each paged table which calls the paging function:
   The query passes the following variables which need to be set: the count of rows within each page, the table name, and a list of the column names to be returned. Once created and applied the data source will be available within your dashboard/excel sheet for use.

It is recommended that to implement these steps the person has some experience of power query and Power BI. However, the user need only be able to use Advanced Editor to edit the initial items at the beginning of each table specific query.

Paging Function source code to use in the reports

In power query open a blank query, name it ‘PageFunction’ and paste the following code:

//function PageFunction
let
   getData = (host_url, API_key, pageSize, tbl, fields ) => // function starts here

   let
      countfield = Text.From(List.First( fields)) , // Should be 'Id' as first in fields list
      countrows = 
         let 
            Source = OData.Feed( host_url , [#"X-API-Token"= API_key ]), 
            TableName = Source{[Name=tbl , Signature="table"]}[Data],
            RemovedColumns = Table.SelectColumns(TableName,{ countfield}),
            totalRows = Table.RowCount(RemovedColumns)
         in
            totalRows, // returns total rows

      pages = Number.RoundUp(countrows / pageSize), // Calculate the total number of pages
      pageNumber= 1, // start the loop
      getPage = (pageNumber) =>
         let
            skip = (pageNumber - 1) * pageSize, // Calculate the number of rows to skip for the current page
            url = host_url & "/" & tbl & "?$skip=" & Text.From(skip) & "&$top=" & Text.From(pageSize) ,
            source = OData.Feed(url, [#"X-API-Token"= API_key ]) ,
            table = Table.SelectColumns(source, fields)
         in
            table,

      pageList = List.Generate(
         () => 1, // Start with the first page
         each _ <= pages, // Continue while the page number is less than or equal to the total number of pages
         each _ + 1, // Increment the page number
         each getPage(_) // Fetch the data for the current page
         ),
      combinedTable = Table.Combine(pageList)
      in
         combinedTable 
in 
   getData // returns combined table

Invoking the paging function for specific tables

No specific naming convention is need

The above PageFunction can then be invoked to import columns for a specific table. Each OData table will need a separate query. Paste and save the sample queries below into separate blank queries

Users Table example:

let    
    API_key = "YOUR_API_KEY" , // or replace with  apikey parameter,
    host_url = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0", // or replace with tenant parameter
    pageSize = 5000,  // Number of rows to fetch per page
    tbl = "Users",
    fields =  { "Id","FullName","Type", "FirstName" },  
    Source = PageFunction(host_url, API_key, pageSize, tbl, fields ) 
    // make sure the function name eg 'PageFunction' 
    // matches the name of the function query named above

Helper Query to get list of fields

Some users may find it easier to use the GUI in power query to get a list of the fields for use in the above paging query.
One quick solution is to use the query below, change the table name as needed and then use "Choose Columns" to select the fields. This action updates the line #"Removed Other Columns. The list from this step can then be copied and used as needed.

let
    tbl ="LearningPlanComponents", 
    url = host_url  &  tbl & "?&$top=10"  , 
    source = OData.Feed(url, [#"X-API-Token"=#"<API key>"]),
    #"Removed Other Columns" = Table.SelectColumns(source,{"Id", "ProgramId", "ComponentName", "DueDate", "CompletedDate", "LastSubmissionDate"})  
    
in
   #"Removed Other Columns"

Examples of values for prompts:

Data query

host url - https://TenantName.aptem.co.uk/odata/1.0 
API Key - cmQtZ3JheUBhcHRlbS5jby51ayIsImlhdCI6MTY4MDc3NzU1NSwiZXhwIjpudWxsLCJ0bnQiOiJQb3dlckJJRGVdghntfgfgtbyIsInNjcCI6WyJvZGF0YSJdfQ.1B-VIdVd0GNe6EE6q_r2EX1c43FEA4bYN0bXiI8--d0
Table Name - LearningPlanComponents
field to count - id
batch size - 5000