Forum Discussion

ackerkris's avatar
ackerkris
Helper I
9 months ago
Solved

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

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:
 
 
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!
 
 
 
 
 

 

 

 

 
 
Dataflow
Need Help
  • v-dineshya's avatar
    v-dineshya
    9 months ago

    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

     

8 Replies

  • v-dineshya's avatar
    v-dineshya
    Community Support
    9 months ago

    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

     

  • ackerkris's avatar
    ackerkris
    Helper I
    9 months ago

    v-dineshya many thx for helping me out with this! Learned a lot more about the Power BI capabilities thanks to you! Also the optimized code is again very helpful!

     

    Kudos to you!

     

    Wkr,

     

    Kris.

  • ackerkris's avatar
    ackerkris
    Helper I
    9 months ago

    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's avatar
      v-dineshya
      Community Support
      9 months ago

      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's avatar
        ackerkris
        Helper I
        9 months ago

        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?
         
  • ackerkris's avatar
    ackerkris
    Helper I
    9 months ago

    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's avatar
      v-dineshya
      Community Support
      9 months ago

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

       

      Regards,

      Dinesh