How to Paginate an OData Service Using Power Query

Have you had issues importing data from an OData service? Has your OData table taken too long to load into Power Query? Have you had data size restrictions from an OData service that made it impossible to load the entire data? Then this blog is for you.

In the era of big data, OData services are storing more and more data to optimize data extraction from the OData service. Pagination is a strategy to ensure smooth and seamless data extraction.

In this blog, we will walk through how to dynamically paginate data from an OData Service using Power Query.

Sections

What Is Pagination?

Pagination for data engineers is the process of dividing large data into manageable data sizes. In the context of this blog, pagination returns data based on user-defined parameters.

For example, if your data contains 10,000 records, and you want to present it in two tables, you will define each table size as 5000.

Pagination helps conserve resources, such as bandwidth or memory, and improve the response time for the OData request and end-user experience.

There are different types of pagination, the one covered in this blog will be offset-based pagination. Offset-based pagination allows the client to define the number of records to skip and the maximum number of records to return in a data query.

Offset-based pagination achieves this by using the $top (maximum record to return) and $skip (record to skip) system query option in OData. Learn more about OData and its query options here.

The Data

This blog will show you how to perform offset-based pagination on a dataset. For this blog, the data we will be connecting to is the Microsoft AdventureWorks sample database via its OData URL.

This URL exposes a limited subset of the AdventureWorks database. Tables available in this OData service are:

  • CompanySales
  • ManufacturingInstructions
  • ProductCatalog
  • TerritorySalesDrilldown
  • WorkOrderRouting

This walk-through example focuses on the TerritorySalesDrilldown table. You can access the OData query to this table here

From here on, this will be referred to as the base query.

Step 1 – Get Total Record Count

The first step to paginate your data is to determine the number of rows in your dataset. You can achieve this by using the $inlinecount system query option that OData provides.

This stipulates that the response must include the total record count in the table request. To learn more about how $inlinecount works please visit this link.

The query used to get the total record count can be found here.

The above query would return the total record count in the table metadata and all the records. This is inefficient, so we will modify the query to return the total record count with only the first row of data.

This is done by adding the $top system query option to our base query found here

To retrieve the row count in Power Query, use the Web connector.

In Power Query, click New Source and select Web in the list of Most Common.

Step 1 – How to Get Total Record Count

Paste the total record count query into the From Web pop-up box and click OK.

Step 2 – How to Get Total Record Count

If the Access Web content pop up box shows up, select Anonymous and click Connect

Step 3 – How to Get Total Record Count

Double click on service.odata.org 721 bytes to open it. A table that contains information about the data is created.

Step 4 – How to Get Total Record Count

Under the column titled http://schemas.microsoft.com/ado/2007/08/dataservices/metadata, click Table. The table contains the total record count for the TerritorySalesDrilldown table.

Step 5 – How to Get Total Record Count

The new table provides the total row count of the table as 3703. 

Step 6 – How to Get Total Record Count

To use the data for pagination, we need to convert the table into a number.

Right-click on row 1 and select Drill Down.

Step 7 – How to Get Total Record Count
Step 8 – How to Get Total Record Count

In the Query Settings pane, navigate to Properties and under the subsection Name change the table name from TerritorySalesDrilldown?$inlinecount=allpage to TerritorySalesDrilldown

Step 9 – How to Get Total Record Count

We have now successfully converted our table to a number, which can be used for pagination. The next step is to create the base query function.

Step 2 – Create Base Query Function

To create the function you must first create a blank query. In Power Query, click New Source and select Blank Query in the list of Most Common.

Step 1 – How to Create Base Power Query Function

In the Query Setting pane, change the Name under Properties from Query1 to Offset Base Query

Step 2 – How to Create Base Power Query Function

Before editing in the advance editor, modify the base query using $top, $skip, and $orderby system query option.

Base Query:
https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown

Modified Base Query:
https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown?$top=&$skip=&$orderby=

Now that the base query has been modified, select the Offset Base Query in the query pane, then click on Home, then Advanced Editor.

Step 3 – How to Create Base Power Query Function

With the Advanced Editor open, connect to the modified base query with the OData connector.

Source = OData.Feed(“https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown?$top=”&Number.ToText()&”&$skip=”&Number.ToText()&”&$orderby=”)

Create parameters that would be used as input values for $top, $skip, and $orderby

Source = OData.Feed("https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown?$top="&Number.ToText()&"&$skip="&Number.ToText()&"&$orderby=")

Create parameters that would be used as input values for $top, $skip, and $orderby
(top_number as number, skip_number as number, orderby as text)=>

Type the newly created parameters as input values for $top, $skip, and $orderby system query option

Source = OData.Feed("https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown?$top="&Number.ToText(top_number)&"&$skip="&Number.ToText(skip_number)&"&$orderby="&orderby)

Offset Based Query M Code:

(top_number as number,skip_number as number,orderby as text)=>
let
    Source = OData.Feed("https://services.odata.org/AdventureWorksV3/AdventureWorks.svc/TerritorySalesDrilldown?$top="&Number.ToText(top_number)&"&$skip="&Number.ToText(skip_number)&"&$orderby="&orderby)
in
    Source

Offset Base Query now becomes a function that accepts input parameters top_number, skip_number, and orderby and retrieves data specified by the entered parameters.

Step 4 – How to Create Base Power Query Function

Step 3 – Create Pagination Function Using List.generate

To create the pagination function, the List.Generate Power Query iterative function will be used. This function will iterate through the initial input values and stop when it reaches the total record count. A simple explanation of List.Generate function and the parameters it accepts are below:

List.Generate (

initial, Initial condition of the function

condition, Condition evaluated for each iteration

next, the next row to be evaluated

optional selector, if the condition is evaluated to true, take that result and do something additional with it) 

For a more detailed explanation on List.Generate please visit the Microsoft documentation here.

Create a blank query as described earlier and name it Paginate Base Query

Step 1 – How to Create Pagination Function Using List.generate

Open Advanced Editor and write the M Code below:

(top_rows_to_return as number, number_of_rows_to_skips as number,column_to_order_by as text)=> 
let    
    Source = List.Generate(()=>
    [Top = top_rows_to_return, Skip = number_of_rows_to_skips,Orderby = column_to_order_by, Table = #"Offset Base Query"(Top,Skip,Orderby)],
    each [Skip] <= TerritorySalesDrilldown, 
    each [Top = [Top], Skip = [Skip] + top_rows_to_return,Orderby = [Orderby], Table = #"Offset Base Query"(Top, Skip,Orderby)]) 
in
    Source
Step 2 – How to Create Pagination Function Using List.generate
  • Line 1: creates parameters for $top, $skip, and $orderby system query option.
  • Line 4: start of list.generate function.
  • Line 5: defines the initial conditions for the function which will be entered via the parameters in line 1. These input values are passed through to the function Offset Base Query which was created in step 2.
  • Line 6: defines the condition to stop the iterative process. It will stop when it reaches the referenced total record count query (TerritorySalesDrilldown) created in step 1.
  • Line 7: defines the condition to be evaluated if line 6 evaluates to false.

Step 4 – Test Functions

Test the Paginate Base Query function by entering parameters and clicking invoke

  • top_row_to_return = 1000
  • number_of_rows_to_skips = 0
  • column_to_order_by = ID
Step 1 - How to Test the Paginate Base Query function

If the Edit Credentials window pops up, click Edit Credentials

Step 2 - How to Test the Paginate Base Query function

Select Anonymous and click Connect on the Access Web content pop up

Step 3 - How to Test the Paginate Base Query function

If you get the error message below

Step 4 - How to Test the Paginate Base Query function

Click on File, navigate to Options and settings, then select Options

Step 5 - How to Test the Paginate Base Query function

In the Options pop up, click Privacy, then select Always ignore Privacy Level settings. Once selected, click OK

Step 6 - How to Test the Paginate Base Query function

Now that the privacy settings have been modified, click the Refresh Preview button on the Home tab

Step 7 - How to Test the Paginate Base Query function

A new list with four rows of the record is created. Each record contains a table with 1000 rows each based on the conditions we specified in the function. To access this record, convert the list to a table by clicking To Table

Step 8 - How to Test the Paginate Base Query function

Click OK, when the To Table Dialog box pops up

Step 9 - How to Test the Paginate Base Query function

Expand the records in the column by the expand icon

Step 10 - How to Test the Paginate Base Query function

In the new drop-down box, only select Table. Then uncheck Use original column name as prefix.  Click OK.

Step 11 - How to Test the Paginate Base Query function

A single table is created that contains all the records in the four tables. Let’s rename the table Invoked Function to Territory Sales Drilldown

Step 12 - How to Test the Paginate Base Query function

If you reached this point, you have successfully paginated your data. Step 5 will show you how to parameterize the base query in the code above.  

Step 5 – Parameterize the Base Query

To make the functions more flexible, create a parameter for the base query.

Click on Manage Parameters then New Parameters

Step 1 - How to Parameterize the Base Power Query Function

In the Manage Parameters box, click New

Step 2 - How to Parameterize the Base Power Query Function

Fill the Manage Parameters pop up box with 

Once filled, click OK.

Step 3 - How to Parameterize the Base Power Query Function

Now replace the base query in TerritorySalesDrilldown, and Offset Base Query with the new parameter Base Query.

Modified TerritorySalesDrilldown Query:

let
    Source = Xml.Tables(Web.Contents(#"Base Query"&"?$inlinecount=allpages&$top=1 ")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"updated", type datetime}}),
    #"http://schemas microsoft com/ado/2007/08/dataservices/metadata" = #"Changed Type"{0}[#"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"],
    #"Changed Type1" = Table.TransformColumnTypes(#"http://schemas microsoft com/ado/2007/08/dataservices/metadata",{{"count", Int64.Type}}),
    count = #"Changed Type1"{0}[count]
in
    count

Modified Offset Base Query:

(top_number as number,skip_number as number,orderby as text)=>
let
    Source = OData.Feed(#"Base Query"&"?$top="&Number.ToText(top_number)&"&$skip="&Number.ToText(skip_number)&"&$orderby="&orderby)
in
    Source

Now the codes and functions can be reused for different OData services without changing the M Code.

Conclusion

In this era of big data, pagination has become necessary to save resources, improve data accessibility, improve request response time for the OData request, and improve the end-user experience.

This blog has provided a walk-through example of how to paginate data. We presented three simple steps to follow, with two bonus steps to show you how to test your code and make your code more dynamic.

Feel free to follow the walk-through example and test out the benefits of pagination for yourself.

Share this post

Let's get started with a consultation

Get started with training