Converting Base64 value to use in pagination

[rixmcx59]

Hello all, I have a working solution that I am looking to improve, The challenge I am running into is this, the api I am connecting to uses page [size] with a max of 100 and page[number] which returns a base64 value like "MQ==" for page one, "Mg==" for page two and so-on. 

Currently I have a table with 1 column, in the column are 31 base64 encoded page numbers, I invoke my function on this table and use column 1 as the parameter, this works great until more data is added and more pages need to be called, I will have to manually add the next page to the table.

My goal is to use one of the solutions offered on youtube but they use list.generate, which uses a page number to iterate through pages to get next page. Hopefully I was clear, the real problem here is where and how to decode the page numbers and use list.Generate to dynamically paginate. Maybe there is a better way to this with this particular api using the result, prev and next.

Thanks for any help

[rixmcx59]

Still trying to figure this out, the below code only returns page one. The "url" parameter is the full url for page one and "Token" is the key

et
    // Base URL
    baseUrl = "https://apis-us.MYAPI.com",
    initialUrl = baseUrl & "/v1/dep/13473/projects?page[size]=100&page[number]=MQ==",
    
    // Function to get data from a given URL with authorization header
    GetPage = (url as text) as record =>
        let
            response = Json.Document(Web.Contents(url,
            [Headers=[Authorization=Token, #"Content-Type"="application/vnd.api+json"]]
)),
            data = if Record.HasFields(response,"data") then response[data] else {},
            links = if Record.HasFields(response, "links") then response[links] else null, 
            nextLink = if links <> null and Record.HasFields(links, "next") then links[next] else null,
            nextUrl = if nextLink <> null and Text.StartsWith(nextLink, "http") then nextLink else baseUrl & nextLink
        in
            [Data = data, NextLink = nextUrl],
    
    // Function to loop through pages and combine data
    GetAllPages = (url as text) as table =>
        let
            firstPage = GetPage(url),
            allData = List.Generate(
                () => firstPage,
                each _[NextLink] <> null,
                each GetPage(_[NextLink]),
                each _[Data]
            )
        in
            Table.Combine(List.Transform(allData, each Table.FromList(_, Splitter.SplitByNothing()))),
    
    // Initial call to get all pages
    result = GetAllPages(initialUrl)
in
    result

[rixmcx59]

@Greg_Deckler here is a snip from the api documentation.

Additionally, responses include the links parameter, which provides URLs for the prev and next attributes (or null values if all of the items are on one page). Those URLs include the above parameters, so you can paste them into subsequent requests if needed.

[rixmcx59]

Yes it does return prev and next like so

This is my function, I had to add RelativePath to avoid the Power BI service error for dynamic datasource

(Page as text)=>
let
   Source = Json.Document(
       Web.Contents(
           "https://apis-us.Mycompany.com/v1/orgs/OrgID/projects",
           [RelativePath = "?page[size]=100&page[number]="&(Page),
           Headers=[Authorization="Bearer ####MyToken####", #"Content-Type"="application/vnd.api+json"]])),
    data = Source[data]

in
    data

[Anonymous]

Hi,

Thanks for the solution Greg_Deckler offered, and i want to offer some more information for user to refer to.

hello @rixmcx59 , based on your description, you can use the list.generate() to get the next page, you can refer to the following link.

powerbi - Paging REST API results in Power Query - Stack Overflow

it offer how to use  list.genetate() to get the next page.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rixmcx59]

With my limited skill I was able to get a result into a table with prev and next as columns using this query

let
    Query1 = List.Generate( ()=>
   [Result = Json.Document(Web.Contents(
       "https://apis-us.MySite.com/v1/orgs/orgID/projects?page[size]=100&page[number]=MQ==", 
       [Headers=[Authorization="Bearer MyKeyxxxxxxx", #"Content-Type"="application/vnd.api+json"]]))],
        each [Result][links][next] <> null,
        each [Result = try Json.Document(Web.Contents([Result][links][next])) otherwise null]),
    Query3 = Query1{0},
    Result = Query3[Result],
    links = Result[links],
    #"Converted to Table" = Record.ToTable(links)
in
    #"Converted to Table"

below is my result

I tried following this How not to miss the last page when paging with Power BI and Power Query (thebiccountant.com) but can't figure out the syntax to include the Headers and Auth part. I will keep trying hopefully someone with a similar api will chime in.

Thanks for any help

[rixmcx59]

Thank you Greg, I will work on adding this to my function and the list.Generate query. It would seem that I need to start with list.Generate to generate the table of the available pages, is that correct? I am still a beginner in M, even with years in Power BI.

Thanks

[Greg_Deckler]

@rixmcx59 I would have to know more about the API you are using. Does it return any information about the "next" page?

[Greg_Deckler]

@rixmcx59 I actually cover this topic on The Definitive Guide to Power Query (M). You can binary encode and decode on the fly using something like this to decode for example:

let
  Source = 
    Text.FromBinary(
      Binary.Decompress(
        Binary.FromText(
          "Base64 encoded text",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    )
in
  Source