Even though we believe data and data models should be managed in robust cloud based environments using Power BI and Power Apps, we all come across times when a specific data task needs to be handled quickly. For many of us Excel will then be the tool we turn to.
Unfortunatly many still view and use the tool based on its traditional features and many of the newer and more powerful features are overlooked. E.g. Power Query and Power Pivot.
In the ‘Get & Transform Data’ group under the ‘Data’ tab you will find the same Power Query tools as in Power BI desktop, providing a nice familiarity between the two tools.
However, as you might know, Excel’s basic spreadsheet size is limited to 1,048,576 rows. Most times, this is probably enough, but increasingly often we need to work with larger datasets, even for ad-hoc work. Of course, we could adjust our SQL query to reduce the number of rows. But if we are still exploring the data it can be really useful to utilise great Excel features like pivot tables.
Enter Power Pivot. This is the feature that allows you to work with millions of rows of data in Excel. It has the same infrastructure (Vertipaq) and design as the Table and Model views in Power BI desktop and allows for creating measures and calculated columns/tables with DAX just as in Power BI. (Unfortunatly the design interface is a little bit older and lacks things like IntelliSense when designing DAX formulas.)
To start using Power Pivot, first ensure it’s enabled in under Excel Options:
It will then be available under its own tab in Excel called ‘Power Pivot’
From here you can add tables already in the excel file to the Power Pivot data model, but then we would be limited to just above 1 million rows per table.
The trick is to load the external data directly to the Power Pivot data model, without loading it into an excel table.
There are two ways to do this, either from inside Power Pivot or by using Power Query.
To load the data through Power Pivot, press ‘Manage’. From the new window that pops up it is easy to find the buttons needed to retrieve data from a database
Alternatively you could achieve the same result by using Power Query. First, go to ‘Get Data’ in Excel:
Then log on to your database and choose tables to load. Then click on ‘Load To…’
Then import to ‘Only Create Connection’. Avoid the other options as these would load the data straight into Excel, activating the row limit. Remember to tick the box to add this dara to the Data Model.
By retrieving data in either of these ways (they are fundamentally the same) you will be able to load millions of rows into the data model and then view this data in Excel using a pivot table.
After the data is loaded, insert a pivot table anywhere in Excel and choose the data model as source:
Remember that the row limit will still apply when drawing up a pivot table, so don’t add all the fields to it at once.
You can find more learning resources for Power Pivot at Microsoft’s support page.