topic Re: Getting sorted data in bunches in DAX Commands and Tips

Getting sorted data in bunches

kroman — Thu, 29 Jun 2023 13:18:39 GMT

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

Re: Getting sorted data in bunches

johnt75 — Thu, 29 Jun 2023 14:08:45 GMT

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

Re: Getting sorted data in bunches

kroman — Fri, 30 Jun 2023 13:41:35 GMT

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

Re: Getting sorted data in bunches

johnt75 — Fri, 30 Jun 2023 13:43:52 GMT

I think it wants to be ABS, not REL

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

Re: Getting sorted data in bunches

kroman — Fri, 30 Jun 2023 13:51:56 GMT

😪

Re: Getting sorted data in bunches

johnt75 — Fri, 30 Jun 2023 14:02:52 GMT

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 ?

Re: Getting sorted data in bunches

kroman — Fri, 30 Jun 2023 14:09:07 GMT

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

Re: Getting sorted data in bunches

kroman — Sun, 02 Jul 2023 18:08:09 GMT

Finaly works with

WINDOW(0, REL, 10 , ABS