PowerBI SmartPivot is our first product for 2019, and we couldn’t be more excited about it. Much like the rest of our PowerBI Tiles family, PowerBI SmartPivot was born as an answer to our clients’ problems prior to being spun into a mass market product. In this case, our clients worked mainly in the retail sector and made extensive use of Excel PivotTables in their daily activity. Their woes included a high dependency on IT staff to connect databases to Excel, the lack of granularity provided by Excel alone and, perhaps most importantly, the time it took to filter their PivotTables when analyzing a wide range of items, a common occurrence in retail.
What is PowerBI SmartPivot and who is it for?
PowerBI SmartPivot is an Excel add-in aimed at professionals who regularly work with PivotTables. PowerBI SmartPivot introduces several features that make their life easier, namely the abilities to connect OLAP data cubes and Power BI to Excel, scan all data in an analytical model, apply filters in bulk instead individually ticking a PivotTable’s checkboxes and create granular table reports with ease.
Downloading and installing
An annual PowerBI SmartPivot license is available at DevScope’s store from 99.99€, but you can try it for free by downloading a full-featured 30-day trial version.
If you purchase a license during the product’s launch window, you can use code POWERBI-SMARTPIVOT-30 at checkout for a 30% discount.
After downloading PowerBI SmartPivot, extract the file and double click it to run the installation wizard. Follow the steps and click finish. Open Excel and you should see a grayed out SMARTPIVOT tab.
Click the License button and introduce the key emailed to the address you used to register for the PowerBI SmartPivot trial. Once validated, all options will become available.
NOTE: if you don’t see the SMARTPIVOT tab in Excel, go to File > Options and click the Add-ins tab.
From the Manage dropdown menu, select COM Add-ins and click the Go button. Make sure the DevScope PowerBI SmarPivot check-box is ticked and click OK.
The SMARTPIVOT tab should now be visible. If you still can’t see it, please email our support team.
Connecting OLAP cubes and Power BI to Excel
In order to connect a Power BI dataset to Excel, you must first open it in Power BI Desktop. Once you do, go to the SMARTPIVOT tab in Excel and click QuickConnect. You should see it in the list of connections available.
Double-click it and confirm the range. The PivotTable Fields will appear in the panel on the right. Select which fields you want to add to your PivotTable or drag them to the preset areas below.
Using the search function
PowerBI SmartPivot’s search function greatly expands on what Excel can do by itself, allowing users to find exactly what they’re looking for, regardless of the complexity of their analytical model.
To use it, select any cell in your PivotTable and click the search icon in the menu. A new pane will open next to the PivotTable Fields selection.
You can place any queries in the search field to find exactly the data you’re looking for. In the example below, we’ll ask PowerBI SmartPivot for the gross margin percentage (GM%) of transportation of Primus in 2014. PowerBI SmartPivot will instantly scan your data model and present you a list of the fields that best match your query.
Check the boxes from the list to add that data to a new PivotTable.
Filtering a PivotTable by a list of values
The more data you have in an analytical model, the harder it is to find exactly what you want. Even when you know where to look, it may take some time picking the values for your PivotTable because Excel only allows you to select or deselect all values at the same time.
This isn’t really a problem when you’re working with a small PivotTable, but individually picking which values to filter is a major annoyance when you’re working with hundreds or thousands of items. This annoyance became apparent to us when working with some of our retail clients. Their PivotTables often had hundreds or thousands of different products in them, which meant spending many minutes checking boxes one by one. Earlier last year, we launched Filter by List for Power BI itself on Microsoft’s AppSource for free and now integrated it in Excel with PowerBI SmartPivot.
Using it couldn’t be simpler. With at least a cell of your PivotTable selected, click the Filter by List icon from the menu to open the Pivot Filter pane. Write or paste a list of values you wish to filter your list by and click Apply Filter.
Your PivotTable will change and reflect those values.
NOTE: If your PivotTable has more than one hierarchy, make sure you select the correct one from the dropdown list.
If you plan on using the same filter recurrently, it’s a good idea to save it. To do so hit the Save Filter button after applying it. PowerBI SmartPivot will save the values in your filter and direct you to a Saved Filters pane, where you can apply, update or delete previously saved filters.
To access your saved filters, select any cell on the PivotTable and click the My Filters button from the menu to open a pane with the list of saved filters.
Creating a granular table from an OLAP cube
The final PowerBI SmartPivot feature we’ll cover in this guide is the ability to quickly create a granular table report by selecting its fields from a list. To do so, select a cell containing one of the results in your PivotTable and click Add Table Report from the menu. A window containing all possible fields will open.
To create a table, pick a field from the Available Fields and add it to the Selected Fields section.
When you’re done, click ok to create your table instantly in a new Excel sheet.
Thank you for your using PowerBI SmartPivot and making it through this (extensive) 101. PowerBI SmartPivot includes a few other features we won’t cover in this 101 because we feel are self-explanatory, but if you need any help using them, let us know.
If you still have any questions regarding PowerBI SmartPivot, please check the product’s FAQs or contact us by email.