Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

9 REPLIES 9
Anonymous
Not applicable

@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
Frequent Visitor

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
Microsoft Employee
Microsoft Employee

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

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors