Grouping Data into Batches for Export from PowerBI Service - Out of Memory

[tom59593]

Hi all,

I'm running into an out of memory error while working with a data set that is roughly 1.8M rows x 100 columns of invoice line items for our company.  This data set additionally contains several calculated columns and measures, and is published in a report to our PowerBI service workspace.  Our users then need to be able to export relevant rows to .xlsx that they can process downstream.  Typically, these users are only interested in a small subset (10-20%) of the 1.8M rows, but sometimes they require a larger sample.  

With PowerBI service's limitation of 150k rows per export, we've had to get creative about enabling these users to export the data.  Our current plan is to split the data set into "batches" of 150k rows each that can then be downloaded and combined (if necessary).   Here's some dummy data to use as an example (just made up some food since it's lunch time):

To this data set, we're adding couple of calculated columns:

1. Condition: True when the row needs to be included in user's export
2. Batch Row Number: Assigns a row number to any row that needs to be exported
   

   Batch Row Number = IF('Accountable Parts List'[Condition]=True(),
       CALCULATE(COUNT('Accountable Parts List'[Index]),
       FILTER('Accountable Parts List', 
           'Accountable Parts List'[Condition]=True() && 
           'Accountable Parts List'[Index]<=EARLIER('Accountable Parts List'[Index]))))

3. Batch: Assigns a batch number to any row that needs to be exported (the 3.1 is the batch size--150k for real data) 
   

   Batch = IF('Accountable Parts List'[Batch Row Number]>0,
       TRUNC('Accountable Parts List'[Batch Row Number]/3.1)+1)

Which gets our dummy data set to look like this:

So our user could select batch 1, export to .xlsx, and repeat for each batch.  The problem is when we attempt to add the Batch Row Number column to our production dataset (1.8M rows), we get an out of memory error.  This makes sense because we're basically filtering the table 3.6M times (2 filter statements).  My question is--is there a better way to achieve this same functionality?  How should I effectively enable users to export ~400k rows to a .xlsx file?

Thanks for any recommendations in advance!

[v-ljerr-msft]

Hi @tom59593,

Here is a good article about how to Extract Tabular Data From Power BI Service to Excel using Analyze in Excel feature. Could you go to check if it helps in your scenario? 

Regards