Forum Discussion
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"
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
9 Replies
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 expandPlease see this video with a walkthrough of a good way to do this.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
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] )
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
Anonymous and Anonymous 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!
