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.

Advertisements