How to easily limit the number of rows in your Excel Power Pivot model
I am currently working with an Excel Power Pivot model with about 20+ million rows in the model, and as there are a lot of Excel reports in the model and some of the calculated fields are pretty complex it takes some time when I move calculated fields, format them or change them.
The model retrieves data from a SQL database so the table queries are either Stored Procedures or handwritten SQL statements.
So in order to quickly reduce the number of rows in my biggest fact table I have added
SET ROWCOUNT 2000
Before my in this case Stored Procedure
And it validates
And after saving and thereby and update – I only have 2000 rows in my fact table
And then when I need the full dataset I use
/* statement */ to turn it into a comment
By the way use the /**/ to add comments to your queries so you create short description on what your query is intended to do.