cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarioB84
Frequent Visitor

Power Query Rest API Paging with @odata.nextLink

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"

 

 

 

 

MarioB84_0-1626427663579.png

 

1 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

 

View solution in original post

5 REPLIES 5
mahoneypat
Super User IV
Super User IV

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

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

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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]
  )

 

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

 

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors