Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Converting Base64 value to use in pagination

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 return...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

Hi,

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

hello Anonymous , 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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