Iterating trough items with steps of 500 items(updated)

[PaulKeijzers]

Hi,

i am in a situation where i need to create a loop 

But it is very important that it is in a query and that it is not a function

Why? I need to schedule the update in PowerBi Pro which does not allow Functions to be scheduled.

for example i know i have 2579 items now i want to have a table autogenerated because the number can also be 3200 or more.

in the table there should be 0,500,1000,1500,2000 2500 as when i devide 2579 with 500 it is 5 times. in case of 3200 i need to iterate 6 times 500.

Now i need this in a table as i need the values somewhere else.

does somebody have a solution for this.

it is a extention for this part:https://community.powerbi.com/t5/Desktop/Custom-RESTful-API-Paging-PowerBI-making-multiple-requests-...

thanks in advance.

[Anonymous · Accepted solution]

How about something like:

//Get the row count of your source table and round down to the nearest 100, then

//loop from 0 up to (just below) that row count in 500's

Offset = List.Generate(()=>0, each _ <= Number.RoundTowardZero(Table.RowCount(SourceTable)/100)*100, each _ + 500),

//Convert the list to a table
OffsetToTable = Table.FromList(Offset, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

[PaulKeijzers]

Thanks this is the solution for my issue!

[v-micsh-msft]

Hi PaulKeijzers,

Apologize to say that currently Power Query (Invoke) Function does not support refresh under Power BI service.

We may vote for the idea below:

Make functions refreshable when the data source is a parameter of the function

By the way, to have the table start with 0 item, enter the table list with

= {0..5}

This would create a list from 0 to 5.

In addition, sharing a blog reference:

Loop through Multiple Web Pages using Power Query

And I am afraid that to have the last list number auto generated can’t be achieved based on what I know, we may need to modify the last number if needed.

Regards