Forum Discussion

Housden996's avatar
Housden996
Helper II
2 years ago

Extracting Paginated API data with unknown pages, extremely slow

HI,

 

I am using the following method to extract Pagniated data from a Rest API:

Changing the API Retun to a function

 

(Page as number)=>
let
    Source = Json.Document(Web.Contents("https://sea-turtle-app-db7nv.ondigitalocean.app/v1/data/189/dimension_responses?start_date=2023-04-01&limit=100&offset="&Number.ToText(Page), [Headers=[Authorization="Bearer -TOKEN"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"evaluation_id", "survey_id", "response_id", "created", "dimension_id", "domain", "outcome_area", "dimension_name", "tense", "respondent_category", "question_text", "value"}, {"evaluation_id", "survey_id", "response_id", "created", "dimension_id", "domain", "outcome_area", "dimension_name", "tense", "respondent_category", "question_text", "value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"evaluation_id", Int64.Type}, {"survey_id", Int64.Type}, {"response_id", Int64.Type}, {"created", type datetime}, {"dimension_id", Int64.Type}, {"domain", type text}, {"outcome_area", type text}, {"dimension_name", type text}, {"tense", type text}, {"respondent_category", type text}, {"question_text", type text}, {"value", type number}})
in
    #"Changed Type"

 

And then i am looping and loading pages untill all have been returned using the following:

 

let
    Source = List.Generate(()=>
[Result = try fdimension_responses(1) otherwise null, Page=1],
each [Result] <> null,
each [Result = try fdimension_responses([Page]+1) otherwise null, Page=[Page]+1],
each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"evaluation_id", "survey_id", "response_id", "created", "dimension_id", "domain", "outcome_area", "dimension_name", "tense", "respondent_category", "question_text", "value"}, {"evaluation_id", "survey_id", "response_id", "created", "dimension_id", "domain", "outcome_area", "dimension_name", "tense", "respondent_category", "question_text", "value"})
in
    #"Expanded Column1"

 

 

Is there a way to do this so it isnt as painfully slow? I have tried pulling data from the past few months and I have the same issue its incredibly slow and I dont even think the data is correct, I think it keeps looping on the offset and returning the same data over and over again.

 

Does any one know where I am going wrong?

 

Thanks

Need Help
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Housden996 ,

    First I think you need to make sure that the query's API is optimized to handle paging requests efficiently. This may be beyond your control, but sometimes the API will provide different endpoints or parameters that are more efficient.
    If your environment supports parallel processing, consider processing multiple pages in parallel rather than sequentially. This approach can significantly reduce the total time required to fetch all pages.

    To address concerns about potentially looping through the same data, check that the data extracted in the current iteration is the same as the previous iteration.

    You can also look at this document: Export Power BI embedded analytics paginated reports API - Power BI | Microsoft Learn

     

     

     

    Best Regards

    Yilong Zhou

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

     

     

     

     

    • Housden996's avatar
      Housden996
      Helper II
      2 years ago

      Thanks for your response. 

      So I dont have access to the dev side of the API but I am in contact with the developer.

      If I would want this to be more optimised would I need to request the API to return all records at once instead of paginated due to optermisation reasons?

      Currently I only have one endpoint and a couple of filters, when running for example, 1 offset and 999 records this runs in a matter of seconds but as soon as I try loop around the pages and return it just falls over. 

       

       When you say run in parrallel I assume this would be having multiple data sources running certain page sections at a time?

       

      Thanks

      Alex