Forum Discussion

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

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

Helper I

9 months ago

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.

Community Support

9 months ago

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