Pagination of a REST API in Power Query using M

Purpose: Load the paginated API data, which loads only 50 rows per page. This api has 2 URL parameters:

a.  limit: Number of rows to load per page

b. skip: Skip number of rows from the start.  0 - skip none; 50 - skip 50 and start the page from 51st row.

and a authorization (bearer) token.

Steps:

1. Invoke the base API  with headers (optional), by clicking  the menu options, Get data -> Web ->Advanced

If the call is successful, PowerBI will open Power Query Editor  and load the results in a table

2. In the left query pane, click on query name

3.  Click on Advanced Editor from the top menu.

4. Select all and copy the contents to clipboard/NotePad.

5. Click on Cancel to exit the editor dialog.

6. Delete this query by right-clicking on the query name and by clicking on Delete option.

7. Create a new query by right-clicking anywhere on the left (Query) pane, to open context menu and select New Query -> Blank Query

8.  Click on Advanced Editor from the top menu.

9. Select all and clear the default templated code.

10. Paste the content copied in Step #4.  Depending upon the transformation, the entries may look similar to this below.

11. Make this as a function, which can be invoked multiple times to handle paging, by adding function parameter. Add a line at the start as shown below.

Please note.
a. the parameter on the first line
b. Check the highlighted area of the url. Url parameter limit=50. In my case, the url parameter indicates that API has to fetch 50 rows per page/call. Your url parameter and limits may be different
c. The function offset parameter is added to the url parameter skip to skip number of rows from the start of whole data. We use Number.ToText to convert the number into text to concat with the url string.
12. Give the  function a name by  adding to the let in. We will manipulate the output of the function (shown below as dot dots) in the subsequent steps

13. Since the function is ready now, the next step is to 
a. Call this function in a loop to fetch all the data, page by page.

b. Determine when to stop looping when all data is retrieved

c. Combine all the results in one table.

To Get total number of rows to fetch
To get total rows in the API dataset, we can call and store  base API call result and later use it's total  attribute. 

here allRows is the variable where I am storing the result of the base API call.

To Loop until all rows are fetched

List.Generate will help us loop and call the function as many times as required.  In my case, the page limit is 50 and so the loop is setup in increments of 50 until all rows are fetched, which is determined by allRows[total] attribute. 

apiCallResults  variable create a list of Tables, as a result of our multiple calls made by List.Generate loop.

To combine all results into one table

Table.Combine(table1, table2 ..) function will help us merge all the result tables. So we will table combine to our output.

Since we got everything here, we  replace the lines with dot dots with this let in block as shown below.

14. This is final assembly

References:

https://learn.microsoft.com/en-us/powerquery-m/web-contents

https://stackoverflow.com/questions/46904641/how-to-get-paginated-data-from-api-in-power-bi

https://gorilla.bi/power-query/list-generate-api-calls/

https://learn.microsoft.com/en-us/powerquery-m/table-combine