Forum Discussion
API JSON URL LIMIT 5000
Hi Guys, I need your help. I am fetching data from vsts.contents. I am getting only 5000 records but it contains more than 5000 records. I used this code from chrome but also it is not working. I don't know where is the mistake.
let
BaseUrl = "https:dev.azure.com/XXXX/xxxxx/_apis/build/builds?api-version=5.0",
EntitiesPerPage = 5000,
GetJson = (Url) =>
let RawData = VSTS.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "&$count=true&$top=0",
Json = GetJson(Url),
Count = Json[count]
in Count,
GetPage = (Index) =>
let Skip = "&$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[value]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table
Anonymous
3 Replies
- mahoneypatMicrosoft Employee
Please see this video for one way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Regards,
Pat
- PhilipTreacySuper User
Hi Anonymous
That code as it is is not valid. The BaseURL is missing // in https:// and Options in the VSTS.Contents call is not defined. I don't see how it is working for you to return 5000 records?
If you have another query that is returning 5000 records, what are the values of EntityCount and PageCount? These determine the number of calls made to the API.
Regards
Phil
- v-yingjlCommunity Support
Hi Anonymous ,
You can refer this simliar issue about returning more rows in power query when using URL:
https://community.powerbi.com/t5/Power-Query/API-JSON-URL-10-000-row-limitation/m-p/1491105
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.