API pagination which will work on Power BI Service

[helspaul]

I'm trying to call paginated API data, publish that PBIX to the Power BI Service and have it still work. I found this site How to Handle API Pagination in Power BI - Syntera, tested the example code and it works. 

I then tried to alter it to my site and hit a bit of an issue. This is the code i've got

let
// Recursive function to call a api based on the initial parameters
FetchData = (query as nullable record) as list =>
let
// Calling the web service with the fixed parameters for headers and relative path while adding the variable query data
Source = Json.Document(Web.Contents(url, [Headers = headers, RelativePath = relative_path, Query = query])),
// Store the retrieved results
data = try Source[tasklists] otherwise {},
// Try to get the next link if it is present and extract the page number
next_page = try Text.Split(Text.Split(Source[next], "?page="){1}, "&"){0} otherwise null,
// if there is a next link, the function calls itself again with the new query parameters to get the next page
next_result = if next_page <> null then @FetchData([page = next_page]) else {}
in
// Combine current page data with next page data to a list
List.Combine({data, next_result}),

url = "https://xxxxxxxxxxxx.teamwork.com/projects/api/v3", 
relative_path = "tasklists.json?pagesize=50",
query = [], 
headers = [], 

// Call the recursive function starting from the base url
results = FetchData(query),
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

On the API used in the demo the next page logic relies on the next page URL being delivered as part of the response

But my site does not have this. Here is an example of a blank response. Tasklists usually carries the content I want to append when I call the earlier pages. 

I've tried multiple ways to increase the page number by i +1.

I've also tried to test whether tasklists is null to end my query, or access "HasMore" to find out when its false and end my query. None have worked

Anyone able to explain how I can make this call a series of pages, and stop when the content stops?

Thanks

Paul

[v-tejrama]

Hi @helspaul ,

Thank you @vojtechsima  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[vojtechsima · Accepted solution]

Hey, @helspaul 

first of all, split the url, so that the it's only uptil the teamwork.com part, the rest should be inside RelatiVEpath.

Every API can handle pagination differently, I built most common approaches and how to deal with them here:

https://www.vojtechsima.com/post/pagination-in-power-query

Your approach is similar to the JIRA Offset I wrote about, feel free to check it out, I put a snippet from there here for quicker access:

populatePagesLG = List.Generate(
()=> 0,
(page) => page <= totalPages,
(page) => page + 1,
(page) => request(page * pageSize, pageSize)
),

Before that tho, built pages:

request = (offset as number, limit as number) =>  

    Json.Document(  

        Web.Contents(  

        "https://simvojtech.atlassian.net/",  

        [  

            RelativePath="/rest/api/3/search?jql=project=BI",  

            Query =  

                [  

                    startAt=Text.From(offset),  

                    maxResults=Text.From(limit)  

                ],  

            Headers=  

                [  

                    Authorization=basic_auth_string  

                ]  

        ]  

        )  

    )  

getTotal = request(0, 0)[total]

pageSize = 100

totalPages = Number.RoundDown( getTotal / pageSize )

[helspaul]

Hi, thanks for your response. I've followed the post on your website and tailored it to the API i'm trying to call. Most importantly it still refreshes in the online Power BI Service

This is what I finalised on

let
request = (page as number, pageSize as number) =>
Json.Document(
Web.Contents(
"https://xxxxxxxxxxxx.teamwork.com/",
[
RelativePath="/projects/api/v3/tasklists.json",
Query =
[
page=Text.From(page),
pageSize=Text.From(pageSize)
]
]
)
),
getTotal = request(1, 50)[meta][page][count],
pageSize = 50,
totalPages = Number.RoundUp( getTotal / pageSize ),
manualPages = {1..totalPages},
populatePages = List.Transform( manualPages, each request(_, pageSize)[tasklists]),
expandPages = List.Combine(populatePages),
#"Converted to Table" = Table.FromList(expandPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"

Thanks

[vojtechsima]

@helspaul , nice good job, looks good.