Forum Discussion
Odata Paging when next link coloumn is missing
Hi!
I have a query I want to run to iterate throught the odata until it has taken data from all the pages. My challenge is that the odata gives a next link until there is no next.
I have build a function that seems to do what I want, but fails at the end when it comes to the last page where there is no next coloumn represented in the response. When calling the function I get the error: The field '__next' of the record wasn't found.
Here is the function / script I am tryging with
(baseuri as text) =>
let
Token = Token{0}[Value],
headers = [Headers=[#"Accept"="application/json", Authorization=Token]],
initReq = Json.Document(Web.Contents(baseuri, headers)),
initData = initReq[d][results],
gather = (data as list, __next) =>
let
//get new link from active uri
newOffset = Json.Document(Web.Contents(baseuri, headers))[d][__next],
//build new uri using the original uri
newUri = __next,
//get new req & data
newReq = Json.Document(Web.Contents(newUri, headers)),
newdata = newReq[d][results],
//add that data to rolling aggregate
data = List.Combine({data, newdata}),
//if theres no next page of data, return. if there is, call @gather again to get more data
check = if newReq[d][__next] = MissingField.Error then data else @gather(data, newUri)
in check,
//before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
outputList = if initReq[d][__next] = null then initData else gather(initData, baseuri),
//then place records into a table. This will expand all columns available in the record.
expand = Table.FromList(outputList)
in
expand
Thanks for your update. In the meantime I manage to find the answer and did some changes in my function.
For future references, this function is now working for me:
(RelPath as text) =>
let
Token = Token{0}[Value],
baseurl = "https://api2.successfactors.eu/odata/v2/",
RelPath = RelPath,
initReq = Json.Document(Web.Contents(baseurl, [Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=RelPath]))[d],
nextUrl = Text.AfterDelimiter(initReq[__next],"v2/"),
initValue= initReq[results],
gather=(data as list, url)=>
let
baseurl = "https://api2.successfactors.eu/odata/v2/",
newReq=Json.Document(Web.Contents(baseurl,[Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=url]))[d],
newNextUrl = Text.AfterDelimiter(newReq[__next],"v2/"),
newData= newReq[results],
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,"__next"),
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),
Contains_Column=List.Contains(Column_Names,"__next"),
outputList= if Contains_Column= true then @gather(initValue,nextUrl) else initValue,
expand=Table.FromRecords(outputList)
in
expand
2 Replies
Hi Anonymous ,
To my knowledge, such error may be caused by using undefined variables/ not existed field.
Since there are many rows refer to the [_next] parameter, I'd suggest you check one by one to find the error.
Here is the official document talking about TripPin 5 - Paging - Power Query | Microsoft Docs that you may refer to
Best Regards,
Eyelyn QinThanks for your update. In the meantime I manage to find the answer and did some changes in my function.
For future references, this function is now working for me:
(RelPath as text) =>
let
Token = Token{0}[Value],
baseurl = "https://api2.successfactors.eu/odata/v2/",
RelPath = RelPath,
initReq = Json.Document(Web.Contents(baseurl, [Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=RelPath]))[d],
nextUrl = Text.AfterDelimiter(initReq[__next],"v2/"),
initValue= initReq[results],
gather=(data as list, url)=>
let
baseurl = "https://api2.successfactors.eu/odata/v2/",
newReq=Json.Document(Web.Contents(baseurl,[Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=url]))[d],
newNextUrl = Text.AfterDelimiter(newReq[__next],"v2/"),
newData= newReq[results],
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,"__next"),
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),
Contains_Column=List.Contains(Column_Names,"__next"),
outputList= if Contains_Column= true then @gather(initValue,nextUrl) else initValue,
expand=Table.FromRecords(outputList)
in
expand