API JSON URL LIMIT 5000

[Anonymous]

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

 @v-yuezhe-msft 

[v-yingjl]

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.

[PhilipTreacy]

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

[mahoneypat]

Please see this video for one way to do this.

Power BI - Tales From The Front - REST APIs - YouTube

Regards,

Pat