Unable to remove more than the top 999 rows in Power Query at a time

[bematth]

I have a large convoluted data set with about 12mil rows. The large amount of data + the complexity of the measures we need for our analysis has resulted in a "not enough memory" error. 

For this section of the report, we are only using about 5mil rows. I'm testing to see if eliminating the other 7mil rows will resolve this issue. I sort the applicable column in PowerQuery and enter the exact number of rows to delete.  But when I apply the change, all qw million rows are removed and leaves us with a blank dataset. 

So far I've only been able to remove 999 rows at a time to avoid this (possibly related to the fact that Power Query only shows a 1000 row preview?). We don't really have time to do this 7,000 times, and in this day and age, certainly shouldn't have to. Is there a way around this?

[bematth · Accepted solution]

It is Star schema, but I did find an easy work-around.

When I enter the number in the box, I can still only enter 999, but if I go into that step and edit that number to whatever number I need it to be, it works just fine.

[edhans]

Glad you found a workaround, but there is still something wrong with the model if it is stumbling over 12M records. Perhaps creating a new thread with some details on what it is designed as and what you are trying to accomplish could help.

[edhans]

I would argue the issue is not the rows, but the data model. I have models with 500,000,000 records and they perform quite well, 12M records is really a small dataset. 

My guess is it isn't modeled as a Star Schema, therefore the DAX is complex to get around that issue, and you are running out of resources.

And before you say "well, my data won't work in a Star Schema" I'd say "Yeah, I used to say the same thing, but then I spent more time on modeling, and pretty much everything can be modeled as a Star Schema. Sure, there are challenges when it comes to slowly changing dimensions, header and detail tables, etc, but there are best practices around those and quite often, you can get to a pure Star Schema on those too."

Microsoft Guidance on Importance of Star Schema

My point is I've been exactly where you are (we all have) but the solution to a model with 12M records that won't run isn't to remove records, it is to fix the model. And that is not a quick fix. But it is the right fix. Then your model will scale to 100M records easily, and larger.