cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tom59593 Visitor
Visitor

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

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):

 

dummy_data_1.PNG

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:

 

dummy_data_2.PNG

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!

1 REPLY 1
v-ljerr-msft Super Contributor
Super Contributor

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

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? Smiley Happy

 

Regards

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 235 members 2,385 guests
Please welcome our newest community members: