Forum Discussion

helspaul's avatar
helspaul
Frequent Visitor
9 months ago
Solved

API pagination which will work on Power BI Service

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 ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    9 months ago

    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:

    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(  
            [  
                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 )
     
vojtechsima's avatar
vojtechsima
Super User
9 months ago

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:

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(  
        [  
            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's avatar
helspaul
Frequent Visitor
9 months ago

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