Issues with recuring API calls that use a dynamically generated cursor for fetching the next pages.

[ackerkris]

Hi,

I've created this function:

(limit as number, employees as text, cursor as text) => let

  ResourceUrl = "https://xxxxx",

  Details = if cursor = "0" then "limit="&Number.ToText(limit)&"&employeeIds="&employees&"" else "limit="&Number.ToText(limit)&"&cursor="&cursor&"&employeeIds="&employees&"",

  Response = if cursor = "0"

    then

      Json.Document(

        Web.Contents(

          ResourceUrl,

            [

              Headers = [

                #"Accept" = "application/json",

                #"Content-Type" = "application/json"

              ],

              Query = [

                limit = Number.ToText(limit),

                employeeIds = employees

              ]

            ]

        )

      )

    else

      Json.Document(

        Web.Contents(

          ResourceUrl,

            [

              Headers = [

                #"Accept" = "application/json",

                #"Content-Type" = "application/json"

              ],

              Query = [

                limit = Number.ToText(limit),

                employeeIds = employees,

                cursor = cursor

              ]

            ]

        )

      )

    ,

  Source = Response,

  SalaryResults = Source[results],

  SalaryCursor = Source[response_metadata],

  OutputRecord = Record.AddField([],"Results",SalaryResults),

  OutputTable = Record.ToTable(OutputRecord),

  ResultSet = Table.AddColumn (OutputTable, "SalaryCursor", each SalaryCursor, type text)

in

  ResultSet

This API returns a JSON with basically 3 values: a list of salaries, a cursor for the next page and an error message. Currently just working with the list of salaries and the cursur for the next page(s).

When I execute this query to obtain 2 salary-lines for 1 employee:

let

  Source = List.Generate ( () =>

    [Result = try #"GetData_Results_&_Cursor_1"(2, "****") otherwise null],

    each [Result] <> null,

    each [Result = try #"GetData_Results_&_Cursor"(2, "****", [Result][SalaryCursor]) otherwise null],

    each [Result][Value]),

  SalaryResults = Source,

  ResultsTable = Table.FromList(SalaryResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

  ResultsTable

It returns me a list:

But I would also like to be able to extract the cursor value separately and use it for my next call(s).

If I try to check on whether the cursor field is empty by doing this check:

each [Result][SalaryCursor] <> null

let

  Source = List.Generate ( () =>

    [Result = try #"GetData_Results_&_Cursor_1"(2, "*****") otherwise null],

    each [Result][SalaryCursor] <> null,

    each [Result = try #"GetData_Results_&_Cursor"(2, "****", [Result][SalaryCursor]) otherwise null],

    each [Result][Value]),

  SalaryResults = Source,

  ResultsTable = Table.FromList(SalaryResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

in

  ResultsTable

I always get this error:

Expression.Error: We cannot apply field access to the type Null.

Details

Reason = Expression.Error

ErrorCode = 10335

Key = SalaryCursor

The feedback from @ImkeF on this page have helped me a lot already (kudos to you!) getting this far:

• https://community.fabric.microsoft.com/t5/Desktop/how-to-create-a-query-that-paginates/td-p/20047

But struggling now to check against null value for the cursor so that I can keep on calling recursively the API until it returns me a null.

Any help greatly appreciated!

[ackerkris]

@v-dineshya 

Many thx once more! Looks promising 👍

Unfortunately haven't had any time today to look further into it and not in the office tomorrow, but I'll apply the changes locally and will keep you posted!

Wkr,

Kris.

[v-dineshya]

Hi @ackerkris , Thank you for the update. Could you please provide ETA (Estimated Time for Arrival) for this thread?

Regards,

Dinesh

[ackerkris]

@v-dineshya 

Hey Dinesh,

>>The issue you are facing is classic for recursive API calls when a field can sometimes be null or not even exist, Power Query tries to access [Result][SalaryCursor] before it checks if [Result] is null, so it crashes.

I learn new stuff every day 😁. Thx!

You've definitely have helped me a huge step forward and I'm getting now more and good results 🎉

But not 'complete' yet. I have a similar situation like in your example: you have 3 pages and on your 3rd page you have Eve, but she doesn't show up in the end result, while I would expect her also to show up in your case:

What's the best way to make sure that the call effectively (in your case) gets executed 3 times so that it also fetches the data from the last page?

Much obliged already!

Wkr,

Kris.

[v-dineshya]

Hi @ackerkris ,

Please refer below updated  pagination logic, Please refer below snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[ackerkris]

Helloe @v-dineshya ,

Apologies for the late reply, but too many unexpected things came popping up last Thursday & Friday and only now found some time to look into it.

I have created the following function:

GetData_Results_w_cursor_new

(limit as number, employees as text, cursor as text) => let

  ResourceUrl = "https://****/salaries",

  Response = if cursor = "0"

    then

      Json.Document(

        Web.Contents(

          ResourceUrl,

            [

              Headers = [

                #"Accept" = "application/json",

                #"Content-Type" = "application/json"

              ],

              Query = [

                limit = Number.ToText(limit),

                employeeIds = employees

              ]

            ]

        )

      )

    else

      Json.Document(

        Web.Contents(

          ResourceUrl,

            [

              Headers = [

                #"Accept" = "application/json",

                #"Content-Type" = "application/json"

              ],

              Query = [

                limit = Number.ToText(limit),

                employeeIds = employees,

                cursor = cursor

              ]

            ]

        )

      )

    ,

  SalaryResults = Response[results],

  SalaryCursor = Response[response_metadata],

  OutputRecord = [

        Value = SalaryResults,

        SalaryCursor = SalaryCursor

    ]

in

    OutputRecord

When I execute it, it does return results and if I drill down further manually, I get 2 lines with Salary Results (as expected). 

So far so good.

Next is a query calling upon that function:

let

  Source = List.Generate (

    () =>

      [Result = #"GetData_Results_w_cursor_new"(2, "***", "0"), Cursor = "0"],

    each

      [Cursor] <> null,

    each

      let

        nextCursor = try [Result][SalaryCursor][next_cursor] otherwise null,

        nextResult = if nextCursor <> null

                      then #"GetData_Results_w_cursor_new"(2, "***", nextCursor)

                      else null

      in

        [Result = nextResult, Cursor = nextCursor],

    each

        [ Salaries = try [Result][Value] otherwise null,

          NextCursor = try [Result][SalaryCursor][next_cursor] otherwise null

        ]

  ),

  ResultsTable =

        Table.FromRecords(

            List.Transform(Source, each [

                Salaries = Text.Combine(List.RemoveNulls([Salaries]), ", "),

                NextCursor = [NextCursor]

            ])

        )

in

    ResultsTable

This gives me the following result:

And this the error:

Expression.Error: We cannot convert a value of type Record to type Text.

Details

Reason = Expression.Error

ErrorCode = 10276

Value = [employeeId = "***", values = {...}]

 

I think it's because my list is (again) a list of records and by adding some quick & dirty 'expansions':

let

  Source = List.Generate (

    () =>

      [Result = #"GetData_Results_w_cursor_new"(2, "***", "0"), Cursor = "0"],

    each

      [Cursor] <> null,

    each

      let

        nextCursor = try [Result][SalaryCursor][next_cursor] otherwise null,

        nextResult = if nextCursor <> null

                      then #"GetData_Results_w_cursor_new"(2, "***", nextCursor)

                      else null

      in

        [Result = nextResult, Cursor = nextCursor],

    each

        [ Salaries = try [Result][Value] otherwise null,

          NextCursor = try [Result][SalaryCursor][next_cursor] otherwise null

        ]

  ),

  #"Converted to table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

  #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to table", "Column1", {"Salaries", "NextCursor"}, {"Salaries", "NextCursor"}),

  #"Expanded Salaries" = Table.ExpandListColumn(#"Expanded Column1", "Salaries"),

  #"Expanded Salaries 1" = Table.ExpandRecordColumn(#"Expanded Salaries", "Salaries", {"employeeId", "values"}, {"employeeId", "values"}),

  #"Expanded values" = Table.ExpandListColumn(#"Expanded Salaries 1", "values"),

  #"Expanded values 1" = Table.ExpandRecordColumn(#"Expanded values", "values", {"canBeDeleted", "change", "payFrequency", "creationDate", "customColumns", "isCurrent", "modificationDate", "payPeriod", "id", "endEffectiveDate", "activeEffectiveDate", "effectiveDate", "base"}, {"canBeDeleted", "change", "payFrequency", "creationDate", "customColumns", "isCurrent", "modificationDate", "payPeriod", "id", "endEffectiveDate", "activeEffectiveDate", "effectiveDate", "base"}),

  #"Expanded base" = Table.ExpandRecordColumn(#"Expanded values 1", "base", {"value", "currency"}, {"value", "currency"})

in

    #"Expanded base"

 I get what I need:

but there migh still be a better/quicker way?

[v-dineshya]

Hi @ackerkris ,

Thank you for reaching out to the Microsoft Community Forum.

The issue you are facing is classic for recursive API calls when a field can sometimes be null or not even exist, Power Query tries to access [Result][SalaryCursor] before it checks if [Result] is null, so it crashes.

Please refer below steps.

1. I have created sample data, In Power Query i have created sample M code for API function, Please refer snap.

2.  Created Pagination logic, Please refer below snap.

3. For testing , I took the parameter values, Please refer in snap.

It will generate the two fields "Value" and "SalaryCursor" as below.

After expanding that two fields, you will get the below results.

Please refer attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh