Query retrieving null data despite no records

[Sankzpower]

Hi guys

I have got this code working for what I need however query still running in the background despite there are no records to pull through. I have pasted the screenshot and code what I have been using but not sure where I am going wrong. The last row of this API call is 238 rows as seen in screenshot however query is still running to acquire more even though API has null values. Also, I would be using online powerbi to refresh dataset eventually so would there any challenge at all?Any experienced members help would be much appreciated. 

I am quite a new user and surprised there were so many tips that I could learn from this community so thanks in advance.

[v-eachen-msft]

Hi @Sankzpower ,

I saw "[Page]+1" and "[Counter]+1" in your query, it will keep running, right? The dataset will always be in the process of increasing. So you could use Table.RemoveRows() funciton to remove row which contains too many "null" values.

[Sankzpower]

@v-shex-msft Thanks for the reply.

yes, the API restricts to bring the data upto 100 rows per call. So, I had to use query to run however the condition goes on an infinite loop like you said where the query retrieves null data even there is no data found after reaching final page. Table.Remove Rows easier but the initial query is to bring in all the data until it reaches the end then table.remove rows kicks in to remove null. This process currently takes ages where I had to wait for longer to refresh. 

However, I have found that each API call retrieves two records as attached in the code below - Items and hasMore record fields. Fortunately, this hasMore contains True or False which determines whether there are more data on the next page. I am just trying different different queries to see how this can be adjusted for example : the loop will continue to run until it hits Hasmore = False field  then query should stop retrieving data as I think this probably the solution. Any of your help would be massive !!

The below is the response of the last page, as you can see webcall retrive items and hasmore record field. 

{
items: [
{
id: 912,
name: "YOd",
utilityCategoryId: 9,
utilityId: 9,
isARec: "N"
},
{
id: 1054,
name: "Yorkds",
utilityCategoryId: 9,
utilityId: 9,
isARec: ""
},
{
id: 1055,
name: "Yu Water",
utilityCategoryId: 9,
utilityId: 9,
isARec: ""
}
],
hasMore: false
}

This is how its shown after retrieving the final data. 

{
items: [ ],
hasMore: false
}