Power Query Rest API Paging with @odata.nextLink

[MarioB84]

Hello everyone,

I need your help. In a Rest API call in Power Query. The API always returns 1000 rows per call and then the next 1000 rowsI have to call the odate.nextLink in a Loop to get all the data. How can I do this in a loop.
Here is what I got from the first call and with odata.nextLink the next URL call is visible

let
  Source= Json.Document(
    Web.Contents(
      "https://Website.net/external/Sales?$skip=0",
      [
        Headers = [
          #"accept" = "application/json",
          #"Authorization"
            = "Api Key"
        ]
      ]
    )
  ),
  #"In Tabelle konvertiert" = Record.ToTable(Source),
  Value = #"In Tabelle konvertiert"{1}[Value],
  #"In Tabelle konvertiert1" = Table.FromList(
    Value,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
  )
in
#"In Tabelle konvertiert1"

[GOEV]

@MarioB84  and @shriti1995 is there a means to modify the above with a Custom Connector that makes an Oauth call to Graph API?

For instance, I will connect with Source = MyGraph.Contents(url) which is already registered but only makes a single call. Please see GitHub - cristianoag/PowerBIGraphCustomConnector: Power BI Graph Custom Connector

Any assistance or thoughts are much appreciated!

Thank you!

[shriti1995]

I found another way to do it. When in the last call the @odata.nextLink is blank instead of Null.

(baseurl as text)=>
let
initReq = Json.Document(Web.Contents(baseurl)),
nextUrl = initReq[#"@odata.nextLink"],
initValue= initReq[value],
gather=(data as list, url)=>
let
newReq=Json.Document(Web.Contents(url)),
newNextUrl = newReq[#"@odata.nextLink"],
newData= newReq[value],
data=List.Combine({data,newData}),
Converttotable = Record.ToTable(newReq),
Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
Column_Names=Table.ColumnNames(Pivot_Columns),
Contains_Column=List.Contains(Column_Names,"@odata.nextLink"),
check = if Contains_Column = true then @gather(data, newNextUrl) else data
in
check,
Converttotable = Record.ToTable(initReq),
Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
Column_Names=Table.ColumnNames(Pivot_Columns),
Constain_Column=List.Contains(Column_Names,"@odata.nextLink"),
outputList= if Constain_Column= true then @gather(initValue,nextUrl) else initValue,
expand=Table.FromRecords(outputList)

in
expand

[mahoneypat]

Please see this video with a walkthrough of a good way to do this.

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

Pat

[mahoneypat]

Usually you can make a $count call to get that by itself.  However, if you know the approximate # to expect, you could adapt the approach I showed to go past that with a fixed # of calls and then just remove the few rows that error.

Pat

[MarioB84]

Hi @mahoneypat Thanks for the link, unfortunately I can't use this as I don't get a value for the total number in my output. And so with always get a text value with the next URL. Here I need a loop until the end is reached and this is no longer displayed

[JonesJonesJones]

Here's the relevant section of code I use for connecting to a graphql API - You will need to do some tweaking to make it line up. The first line calls another function which grabs one page, then the List.Generate keeps running through the pages until the data values = null.  Hopefully it will give you something to start with.

fngetpages = (nextpg) as record =>
let 
    Source = fxGetPages(QueryType, Fields, Campaign, additional, nextpg),
    data = try Source[data] otherwise null,
    next = try data[pageInfo] [endCursor] otherwise null,
    res = [Data = data, Next=next]
in
    res,

   GeneratedList =
  List.Generate(
      ()=> [res=fngetpages(null)],
      each [res] [Next] <> null,
      each [res = fngetpages([res] [Next])],
      each [res] [Data]
  )

[MarioB84 · Accepted solution]

Thanks for your help, i have configure your examples and found a good solution, what works fine for mee. With activation of the @odata.count i get the max number an loop this by the entities per page. and append each one to a large table

let 
    BaseUrl         = "SampleAPIUrl",
    ApiKey           = "SampleKey",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [
        Headers = [
          #"accept" = "application/json",
          #"Authorization"
            = ApiKey
        ]
      ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "?$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "?$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip,
            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

[blazor]

How would you write this if you dont have the count available and only

@odata.nextLink?

in a way yo call the nextLink until there is no nextLink in the response

[brunno_santos]

Although it is marked as Solved, this link will guide you using @odata.nextLink attribute, so you don't need any further customizations in your query.

TripPin 5 - Paging - Power Query | Microsoft Docs