Error handling in paginated REST API call - ServiceNow

[spocx]

Hi  community

A couple of years ago, the amazing @ImkeF helped develop this query that could paginate ServiceNow data until the end.

It has been working pretty good, but I often get the following errors when getting the data which is stopping my dataset from refreshing.

• We found an invalid character between object key and value in JSON input
• We cannot convert the value "" to type Record.

I am no expert in power query, so I was hoping that the communty might be able to help.

The code I am currently using:

let
Pagination = List.Skip(List.Generate( () => [WebCall=[result = {0}], Page = 0, Counter=0],
//Start Value
   		each List.Count([WebCall][result])>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://service-now.com/api/now/table/incident?sysparm_limit=3000&sysparm_offset=1", 
                                    [Query=[sysparm_offset =Text.From([Page])]])), // retrieve results per call
     			Page = [Page]+3000,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall"}, {"WebCall"}),
    #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"result"}, {"result"}),
    #"Expanded result" = Table.ExpandListColumn(#"Expanded WebCall", "result")

in
    #"Expanded result"

In another thread I found this example of code that is very similar and with an error handler, but my coding skills is not good enough to reuse the code:

let
Pagination = List.Skip(List.Generate( () => [Table =  #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value
   		each  Table.RowCount([Table])>0 or [Counter]=0 , // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("link"],Query=[page=Text.From([Pages])]])),
// retrieve results per call
     			Pages = [Pages]+1,
     			Counter = [Counter]+1,// internal counter
     			Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query
                      ]   ,
                each [Table] 		
), 1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

[spocx]

Thank you for the answer @lbendlin But I am not sure I understand. Should I use your code example to troubleshoot the errors I get?

[lbendlin]

yes, the code example demonstrates both approaches 

try Json.Document(...) otherwise "ok"

which tries to fetch a JSON string (with dataset refresh error details) but falls back to "ok"  if that fails, and 

Web.Contents(URL,[ManualStatusHandling = {400}])

which says to ignore error 400 for this scenario.

[lbendlin]

Familiarize yourself with the concept of  " try ... otherwise ... "  for Power Query related errors.  For actual HTTP call errors you can specify ManualStatusHandling,  indicate which codes you want to handle manually, and then retrieve the error details as needed.

	datasources = Table.AddColumn(Expanded, "statusJSON", each try Json.Document(Web.Contents( "https://api.powerbi.com/v1.0/myorg/gateways/"&[Gateway ID]&"/datasources/"&[Gateway Datasource ID]&"/status",[ManualStatusHandling = {400}]),65001) otherwise "ok"),
    details = Table.AddColumn(datasources, "Error Message", each try List.Select([statusJSON][error][pbi.error][details], each _[code] ="DM_ErrorDetailNameCode_UnderlyingErrorMessage"){0}[detail][value] otherwise null, type text),