Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

API JSON URL 10,000 row limitation

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

Need Help

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

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

      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's avatar
    PhilipTreacy
    Super User
    5 years ago

    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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

    • PhilipTreacy's avatar
      PhilipTreacy
      Super User
      5 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.