Getting sorted data in bunches

[kroman]

Hi All

We have a dataset with 100K rows in the following format:

And we have a flow that is getting data from this dataset

Due to the fact that flow cannot get all 100k rows in one go we are getting it in bunches of 25k rows, using autoincremented index, after that we are creating csv file from it - no problems so far.

But we also need to be able to sort  data by any of those columns and then get data in 25k rows bunches, so we cannot use min-max ID for that any more 😞 , and becouse we have a lots of columns, creating index for each column is not an option, all to be done dynamically.
 So the question is - how can we get 100k rows of dynamicaly sorted data from dataset into flow ?

Thanks

[johnt75 · Accepted solution]

You could use the WINDOW function, that allows you to order by whichever column you want and you can provide the offsets you need.

[kroman]

Finaly works with 

WINDOW(0, REL, 10 , ABS 

[kroman]

Thanks for your reply     

  I am trying it with WINDOW with following query on a 100 rows table

     EVALUATE  
         VAR Window1 =  WINDOW(1, REL, 5 , REL, Data100Rows, MATCHBY([Index]))
    RETURN Window1

But it returns all 100 rows instead of 5 rows

[johnt75]

I think it wants to be ABS, not REL

EVALUATE
VAR Window1 =
    WINDOW ( 1, ABS, 5, ABS, Data100Rows, MATCHBY ( [Index] ) )
RETURN
    Window1

[kroman]

😪

[johnt75]

If you're getting all the data each time, does it matter what order it is in ? Also, have you considered using a Live Connected xlsx file rather than exporting to CSV ?

[kroman]

100 rows is just for testing
we have a 200k rows dataset which we need to convert into csv with a Flow (powerautomate), and flow can only take 25-30k rows in one go, so I am doing some looping to get all the data. But we also need to sort that data by different columns before getting it to csv