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)&...
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

     

    Invoke_new.pbix20 KB
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?
 
v-dineshya's avatar
v-dineshya
Community Support
9 months ago

Hi ackerkris ,

Thank you for the update. Please refer below optimized M code.

 

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
]
),

PaginatedTable = Table.FromRecords(Source),
ExpandedSalaries = Table.ExpandListColumn(PaginatedTable, "Salaries"),
ExpandedAll =
let
firstRecord = try ExpandedSalaries[Salaries]{0} otherwise null,
fieldNames = if firstRecord <> null then Record.FieldNames(firstRecord) else {},
expanded = Table.ExpandRecordColumn(ExpandedSalaries, "Salaries", fieldNames, fieldNames)
in
expanded,
ExpandedValues = Table.ExpandListColumn(ExpandedAll, "values"),
ExpandedValuesDetails =
let
firstValue = try ExpandedValues[values]{0} otherwise null,
valueFields = if firstValue <> null then Record.FieldNames(firstValue) else {},
expanded = Table.ExpandRecordColumn(ExpandedValues, "values", valueFields, valueFields)
in
expanded,
ExpandedBase =
let
firstBase = try ExpandedValuesDetails[base]{0} otherwise null,
baseFields = if firstBase <> null then Record.FieldNames(firstBase) else {},
expanded = Table.ExpandRecordColumn(ExpandedValuesDetails, "base", baseFields, baseFields)
in
expanded
in
ExpandedBase

 

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

 

Regards,

Dinesh