Forum Discussion
Anonymous
3 years agoNot applicable
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: "Num...
- 3 years 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.
Anonymous
3 years agoNot applicable
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 🙂