API JSON URL 10,000 row limitation

[Anonymous]

Hello,

I'm new to power BI and I want to know if this is possible.

We are pulling from a link, but the downside is we can only pull 10,000 row at a time.
How can I pull more than 10,000 row?

On the URL its only required me to put the Token Code and the required StartDate and End Date.

Thanks

[mahoneypat]

If your API has the option to include a $skip or $skiptoken in the url, you can generate a list of numbers increasing by 10,000 up to the # of rows you have in the data, and then concatenate the value on that row into the web call.  Then expand the column of Tables to combine all the results.

List.Numbers(0,10, 10000) //for example

Convert to table, then add a custom column with the web.contents step (embedding the new number column in for the skip value.

Regards,

Pat

[Anonymous]

@mahoneypat 
Okay i will ask the vendor if they allow skip

@PhilipTreacy 
I will test this out if it works.
Sometimes if i pull per day, the problem is sometimes 1 day of data is too much and sometims exceed the 10,000 limit. thats the one im concern. But ill get back to you, i will test it out.

[PhilipTreacy]

Hi @Anonymous 

I've modified my original code to more closely match yours but you will still have to make some modifications.

let
    url= "https://jsonplaceholder.typicode.com/todos/1",

    GetPage = (Page) =>

        let
            Source = Json.Document(Web.Contents(url,
                [
                    Query = [ 
                                StartDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), - Page),"yyyy-MM-dd"),
                                EndDate = Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), - 0),"yyyy-MM-dd")
                            ]
                ]
            ))

        in  Source,
 
    PageIndices = { 1 .. 5 },
    Pages = List.Transform(PageIndices, each GetPage(_))
in
    Pages

You originally said you have to specify StartDate and EndDate so these parameters are included as part of the Query record.  Add your token parameter name and value into this record and the entier query string is built for you, no messing about with "" and &

I'm not sure of the exact syntax you're using to get data for other days e.g. Source =  "source"& Today -1  but you could also add this into the Query record.

PageIndices is the list that holds the number of days that you want to get data for.  In my code it holds values from 1 to 5, change the 5 to however many days you need.

This query will get all responses from the website and store them in Pages where you can do subsequent transformations like convert the list to a table.

The above query will get dummy JSON replies from the typicode.com website so you can get an idea of what your results will look like.

If you need more assitance with this just post back and @ mention me so I see the reply.

Cheers

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[PhilipTreacy]

Hi @Anonymous 

Here's an example of pagination, this query gets the blog post titles from the first 5 pages of the Microsoft Excel blog

let
    url= "https://www.microsoft.com/en-us/microsoft-365/blog/excel/page/",

    GetPage = (Page) =>

        let PageNum = Text.From(Page),
            Source = Web.BrowserContents(url & PageNum & "/"),
            HTML_Table = Html.Table(Source, {{"Column1", ".entry-date"}, {"Column2", ".card-title"}, {"Column3", ".my-3 *"}, {"Column4", ".bg-gray-100 .cta"}}, [RowSelector=".col"])

        in  HTML_Table[Column2],
 
    PageIndices = { 1 .. 5 },
    Pages = List.Combine(List.Transform(PageIndices, each GetPage(_)))
in
    Pages

I know that I can access pages on the blog by using /page/x at the end of the main url.  I use List.Transform to call the Getpage function 5 times as specified in PageIndices.

Exactly how your API deals with pagination shoudl eb set out in the API documentation.  Check it out and if you need more helpjusy post back here.

regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Unfortunately the API Documentation didnt have any stuff related to pages.

The only work around I made is to pull by day.

Source = "source"& Today
Source =  "source"& Today -1 
Source =  "source"& Today -2

and so on and then append it.

But not sure if theres a neat way or efficienct way to do this.

[PhilipTreacy]

Hi @Anonymous 

The API should provide some way for you to specify paging - usually a query parameter where you specify what page you want e.g. page=1, page=2 or something similar.

Using this you can specify page=2to get the 2nd 10,000 rows.

You can then use a loop to get the required rows.

Phil