Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 buil...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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