GetData from Web API limiting to 10 rows

[BobN]

I am relatively new to Power BI and am certain that this is just a beginner issue but I cannot solve this.

I connected to the Rest API by using WEB connector.

The data pulls down and reports that there are 25 rows.

It is JSON data

I clikc on the List in the first row

The next screen resolves to 10 rows of record with a header that says list. 

Is it only showing 10 rows because this is a preview of the data? Or is this where I have an issue?

I right click on the top row and convert to table. 

Again, 10 rows with a header that says Column1 and a split icon

I expand the Column 1 and get my nice table....but it is only 10 rows.

I select Close and Apply and my report now has the data loaded but only 10 rows.

Note: I can send different parameters to the API and get different data but I am always only getting back a max of 10 rows. When I use Postman my GET returns 25 rows just as reported by the query in Power BI....

Any ideas are appreeciated.

[mtlforlife]

Echo this same issue. I'm using the Web connector against a web page that includes 2 HTML tables with more than 10 records each, but Power Query trims the dataset to 10 records. I can read the source code and see the HTML code. I tried modifying the Power Query M Web.Contents() and/or Web.BrowserContents() before being passed to Web.Page(), verifying the same code, reducing the code to just the </table> tags thinking client side script may have been trimming the records, but to no avail.

Any help/insight would be appreciated, please let me know if you need more details. My source is an intranet page, so I can't directly offer it for trouble shooting.

[rbiggs]

Did you try passing the paging variable over?  That was the issue I encounted.  My API had a default of 10 records until I added this.  Please see my resposne below.

Rick

[mtlforlife]

Thank you, I tried this suggestion from prior review of the post. It does not change the dataset return limited to 10 records. The HTML return in a browser, Postman and Power Query has the compliment of all records exceeding 10, but the trim seems to be occurring in the Html.Table() call or somewhere else in Power Query.

[rbiggs]

I too am getting this very issue. Using Postman I see all my records, but in PowerBI I am only showing 10 records.

Look forward to seeing a solution.  Thanks.

[rbiggs]

I found a solution that solved my issue.  The cause is not with PowerBI but rather with the RESTapi I was dealing with.  The RestAPI had a limitation of 10 rows unless I added the following to the end of the REST URL call.

https://*****.com/wp-json/gf/v2/entries?form-12&paging[page_size]=1000

&paging[page_size]=1000

This satisfied the issue I was having.  It is now pulling all the records in the system now.  Note:  I only at this current time have 77 and they are all coming.

I hope this helps out!

[quickfix27]

I also have this issue when connecting to data using web connector URL.  Data should have thousands of rows, but query seems to default to just 10 rows of data.  Any idea how to edit this default?

[Greg_Deckler]

I don't suppose that you could post the URL to the API or the raw JSON that you are getting back?

[kreillyNB]

Hi Greg.  I'm having the same issue - trying to pull down a long list of orthopaedic surgeon data from an API source (national provider identifier database), but only getting 10 back.  Here is the API: https://npiregistry.cms.hhs.gov/api/?version=2.1&taxonomy_description=orthopaedic+surgery

[BobN]

Thanks for your response.

It is a MIRAKL API and even though it is a test system I cannot post the full url since it has a key which needs to remain private.

But this is a good hint. I should try another API that is public and see if I have the same problem.

Follow up Question. I can see the JSON as I work with it in query editor. the first step in query editor has the top level of the JSON displayed as LIST and row count of 29....how would I get the value of LIST? e.g. the raw JSON.  I do see it fine in Postman.  

[BobN]

I added an API for beer recipes https://api.punkapi.com/v2/beers It has a default limit of 25 rows. All of these pulled into power bi. I am sort of stuck. In postman for the mirakl api I am getting 29 rows but in Power bi only 10 display. However the initial view says there are 29 rows.  How would I  view the raw JSON?

[BobN]

To help put my dialog in context.

The 1st step in query editor (Source) shows 29 records and the next step only shows 10 after I click List

Source

[Jaxem]

Did this ever get figured out? I'm trying to pull data from the MailChimp API and having the same issue. 1500+ members in the list, but only 10 getting pulled down.

[jakec]

Did you ever have any luck? I'm also trying to pull data from MailChimp API and only getting 10 rows...

[QueryWhiz]

Hi @jakec As a workaround, maybe you can use a 3rd party connector. It will pull the data from the API too but you won't have the issue of only getting 10 rows or a limited amount of rows. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Mailchimp connector in the data sources list:

After that, you will have to first create a secret API key in your Mailchimp account and then using your credentials add your account to the platform.

Once you’ve done this, you can proceed and go to the “Preview and destination” step

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.