404 error when loading data from REST API which paginates to model

[Anonymous]

Hi All,

I am pulling data from REST API that is paginated with the below M Query,

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

However the last record is a ERROR record and the error message is as follows,

DataSource.Error: Web.Contents failed to get contents from 'http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246' (404): Not Found
Details:
    DataSourceKind=Web
    DataSourcePath=http://py-soconnect.fusesport.com/api/members/for-season/250
    Url=http://py-soconnect.fusesport.com/api/members/for-season/250/?page=246

 I am unable to load the data into the model. Any help with this be highly appreciated.

Thanks in advance.

[v-shex-msft]

Hi @Anonymous,

After I test with url you shared, I found your api will return both previous and next request url at same time. I think you need to add condition to check response contents to confirm if it contains next page result.

let
Pagination = List.Skip(List.Buffer(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
)),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

BTW, Buffer function is used to solve the memory issue for looping functions when it save change and generate data model.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft, 

I see that there is a data loss with the above code as the records in the last page are not pulled. 

  "count": 24628,
  "next": "http://py-soconnect.fusesport.com/api/members/for-season/250/?page=2",
  "previous": null,
  "results": [

 The total number of records are 24628 and thus 246 pages , but this query returns only 245 records. Could you suggest a way to get records from the last page as well.

Thanks

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I modify my condition and double check to confirm it can get the last page records:

let
Pagination = List.Skip(List.Buffer(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each try Record.Field([WebCall],"next")<>null or (Record.Field([WebCall],"next")=null and [Counter]>0) otherwise false or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("http://py-soconnect.fusesport.com/api/members/for-season/250/?page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
)),1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table"{19}[Column1]
in
    Column1

Regards,

Xiaoxin Sheng

[Sankzpower]

Hi @v-shex-msft 

Hope you can shed some light. The API restricts to bring the data upto 100 rows per call. So, I have found the below query to run however the condition goes on an infinite loop where the query retrieves null data even there is no data found. For example as shown in the image below.  I have found that there is a record.field called "hasMore" - True or False (attached images - Row 7 contains hasmore as True and Row 8 contains has more as False) which determines whether to see if there are more data on each page. I am just trying different different queries to see how this can be adjusted for exam: when the query hits Hasmore = False  then query should stop retrieving data as I think this probably the solution

let
Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 0, Counter=0], // Start Value
   		each List.Count(Record.FieldNames([WebCall]))>0 or [Counter]=0, // Condition under which the next execution will happen
   		each [ WebCall = Json.Document(Web.Contents("https://energycloud.com/api/Supplier?apiKey=a1459b&orderby=name&page="&Text.From([Page])&"")), // retrieve results per call
     			Page = [Page]+1,
     			Counter = [Counter]+1// internal counter
]
) ,1),
    #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
    #"Expanded WebCall" = Table.ExpandRecordColumn(#"Expanded Column1", "WebCall", {"items", "hasMore"}, {"items", "hasMore"})
in
    #"Expanded WebCall"

.