Api REST Power BI loop

[Pitou]

Hi all,

In Power query i use the GET function of an API REST. It works fine but the API restrict the response to 1 000 entry. So to get every entry, i duplucated my query and use the offset and limit parameters then add all my query.

As i'm a not a power Query/BI professionnel (Work In Progress) i'm pretty sure there is a better way to do that faster and lighter.

Here's the ugly part :

let
    // URL de la requête avec la clé API
    url = "https://sxXXXXXXX/timesheet-lines?sort=id-&offset=0&limit=1000&api_key=XXXXXXX",
    
    // En-têtes de la requête
    headers = [
        #"Content-Type" = "application/json",
        #"Cookie" = "JSESSIONID=4745XXXXXXXXXD7.jvm1"
    ],
    
    // Exécution de la requête GET
    response = Web.Contents(url, [
        Headers = headers
    ]),
    
    // Conversion de la réponse en JSON
    jsonResponse = Json.Document(response),
    // Conversion de la réponse en JSON
    #"project-activity" = jsonResponse[#"timesheetlines"],

Then is use 

let
    // URL de la requête avec la clé API
    url = "https://sxXXXX/timesheet-lines?sort=id-&offset=1000&limit=1000&api_key=8e529cc63e220c2c7a02eb706986bbbd",
  

And so and so...

Can anybody help me ? I'v seen many request about it but i wasn't able to make them work in my case.
 

Thank You.

[Pitou]

Looks great, how do i manage that with the header, cookie and the web.contents ?

What will be the code for the pagination of "timesheet-lines" ?

[vojtechsima]

@Pitou 
well based on the information you gave us:

let
    // Define API request as a reusable function
    request = (offset as number, limit as number) => 
        Json.Document(
            Web.Contents(
                "https://sxXXXX/",
                [
                    RelativePath = "timesheet-lines",
                    Query = [
                        sort = "id-",
                        offset = Text.From(offset),
                        limit = Text.From(limit),
                        api_key = "8e529cc63e220c2c7a02eb706986bbbd"
                    ]
                ]
            )
        ),

    // Get total number of records
    getTotal = request(0, 1)[total], // Assumes the API returns a `total` field in the response

    // Set page size and calculate total pages
    pageSize = 1000,
    totalPages = Number.RoundDown(getTotal / pageSize),

    // Generate list of pages
    pages = {0..totalPages},

    // Fetch data for each page
    populatePages = List.Transform(
        pages,
        each request(_ * pageSize, pageSize)[data] // Assumes `data` contains the records
    ),

    // Combine all pages into a single list
    expandPages = List.Combine(populatePages)
in
    expandPages

[Pitou]

I have an error, the entry "total" is not present in the record.

I guess it's from the line 20

[vojtechsima]

Well, yeah, I don't know your API, you need to know if you have [total] record or not, that's part of the initial response. If you don'T have that and you have different field that tells you how much in total record you have, you gotta use that one. 
Alternatively, make a lot pages upfront to make sure you won'T miss anything and then delete the empty ones (this solution bypass the need for [total] field, so you can skip that step)

[Pitou]

Here's what i get without [total]

I'm sorry but I don't realy understand what I do.

[Anonymous]

Hi @Pitou 

Did vojtechsima 's reply help you? If so, could you please accept it as a solution? If you solved the problem with your method, could you please share your method? This will help more users who are facing the same or similar difficulties. Thank you!

If the problem persists, please feel free to let us know.

Best Regards,
Yulia Xu

[vojtechsima]

@Pitou 

first get some sample data from the request, I guess the "timesheetlines".

then build a list of 0..100, then for each page using List.Transform(list, each ...) call the API using different offset and then you'll see how many records you'll have.

I am sorry, I don't know your API, so you have to do some heavy lifting modifying my general approach.

[Pitou]

Hi @vojtechsima ,

Indeed i get data from the "timesheetlines".

Where should i add the List.transform ?
The number of records will change every day. My company sell services and every employee (200) have to fill a timesheet for every activity they did every week. So the database is growing every day.

[vojtechsima]

Hey man, @Pitou ,

you've got to build a paging mechanism for this.

You calculate pages based on max result, so 4500 total result divided by max result for page (1000), so you have 4 or 5 pages (based on the starting index) and then for each page you create use value of the page as offset.

I have an article that will help you set it up correctly:
https://www.vojtechsima.com/post/pagination-in-power-query


find section "Offset & Limit in PQ"