Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
twiggem
Frequent Visitor

Power Query - Number must be either non-negative and less than or equal to Int32.MaxValue or -1.

I am running an Rest API through Power Query, using List.Generate. It was working very happily until suddenly it is pulling through pages with null values, resulting in the following message:

"Number must be either non-negative and less than or equal to Int32.MaxValue or -1."

 

I have a function fJobs:

 

let
 Source = (Page as number)=>

let
    Source = Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?page="&Number.ToText(Page)))

in
    Source

in
    Source

 

 and then the normal query with the list generate

 

 

let
    List = List.Generate(()=>
//Where to start and which page we are on //if error we want it to return a null (Try)
[Result = try fJobs(1) otherwise null , Page = 1],
//We want this to keep running until the result is null
each [Result] <> null,
//condition for creating the row and itterate throug until it is null
each [Result = try fJobs([Page]+1) otherwise null , Page = [Page] + 1],
// create the output
each [Result]),
    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "name", "requisition_id", "notes", "confidential", "is_template", "copied_from_id", "status", "created_at", "opened_at", "closed_at", "updated_at"}, {"id", "name", "requisition_id", "notes", "confidential", "is_template", "copied_from_id", "status", "created_at", "opened_at", "closed_at", "updated_at"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column2", each ([id] <> null))

in
    #"Filtered Rows"

 

 

Any ideas why this is now pulling throug null values? It is happy until I "Close & Apply" the refresh triggering an issue with the null values. 

It is Greenhouse Applicant Tracking System that I am connecting to

1 ACCEPTED SOLUTION

As you can imagine it is not possible to help much more without access to the API. Try adding more robustness to the exit check in the List.Generate part.  In addition to checking for null you should also check for an empty list.

View solution in original post

7 REPLIES 7
twiggem
Frequent Visitor

Thank you @lbendlin you are a legend! That solved it 🙂

each List.IsEmpty([Result]) <> true,

 

let
    List = List.Generate(()=>
//Where to start and which page we are on //if error we want it to return a null (Try)
[Result = try fJobsPosts(1) otherwise null , Page = 1],
//We want this to keep running until the result is null
each  List.IsEmpty([Result]) <> true,
//condition for creating the row and itterate throug until it is null
each [Result = try fJobsPosts([Page]+1) otherwise null , Page = [Page] + 1],
// create the output
each [Result]),

    #"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "active", "live", "first_published_at", "title", "location", "internal", "external", "job_id", "content", "internal_content", "updated_at", "created_at"}, {"id", "active", "live", "first_published_at", "title", "location", "internal", "external", "job_id", "content", "internal_content", "updated_at", "created_at"}),
    #"Expanded location" = Table.ExpandRecordColumn(#"Expanded Column2", "location", {"name"}, {"Location"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded location",{{"updated_at", type datetime}, {"created_at", type datetime}, {"first_published_at", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"title", "Job Title"}, {"first_published_at", "First Published"}, {"id", "Job Post ID"}, {"active", "Active"}, {"live", "Live"}, {"internal", "Internal"}, {"external", "External"}, {"content", "Job Content"}})
in
    #"Renamed Columns"



Thanks so much for your help 🙂 

twiggem
Frequent Visitor

Thank you @lbendlin I was able to refactor the function, but not sure what you mean with the [Query = [page = Page]] attribute?

Once I have refactored the function, the same error still appears when I load it in desktop.

lbendlin
Super User
Super User

Looks benign. The fJobs function can be severely refactored

 

(Page)=>Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs?page=" & Text.From(Page)))

 

You should use the [Query = [page = Page]] attribute. Otherwise this may not work in the service

@lbendlin I think I have made your amendments as suggested

 

 

 (Page)=> Json.Document(Web.Contents("https://harvest.greenhouse.io/v1/jobs" as text,
[Query = [page = Number.ToText(Page)]]) )

 

 This works ok in the PowerQuery screen, as the previous function did too. It is only once loaded that I receive the same error message again:

Int Error.PNG

Thanks for your help, any other tips that might help? I did read in the API documentation:

API methods that return a collection of results are always paginated. Paginated results will include a Link (see RFC-5988) response header with the following information.

  • next. The corresponding URL is the link to the next page.
  • prev. The corresponding URL is the link to the previous page.
  • last. The corresponding URL is the link to the last page.

Note that when this header is not set, there is only one page, the first page, of results.

But I am not sure how to adjust the query for this

Don't use Number.ToText.  Use Text.From 

NullRows.PNG

Thank you @lbendlin that still brings in the empty rows. I have tried removing empty rows, filtered out null values etc. but the it then shows error and removing the errors brings up the Int32 issue. 

I think the issue lies with the pagination looping endlessly, whilst no values or records are actually contained in the list. It was working fine a few weeks ago

As you can imagine it is not possible to help much more without access to the API. Try adding more robustness to the exit check in the List.Generate part.  In addition to checking for null you should also check for an empty list.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors