Excel’s default drill-through for pivottables connected to a datamodel or Power BI dataset is often not very helpful. The add-in PowerDrill for Excel replaces Excel’s drill-through and gives you full control over the drill details.
In Power BI we have seen many improvements on the Analyze or Drill functionality, and as a developer we have full control on what and how we show the user. In Excel however, not much has changed: the drill-through or Show Details function is still as ugly as it was in 2013. But now you can use PowerDrill for Excel! It offers functions you need to take full control of the drill-through in a very simple way. PowerDrill is basically intended for developers, to validate reports, but is also useful for end-users.
How it was… and still is
In this post I use a pivottable that is connected to a datamodel with data from AdventureWorks:
When you double-click on TotalSales for Cleaners in 2008 then the result is this table:
This “classic view” is not very helpful. The issues are:
- long and difficult to read header names
- not the columns I want to see
- no easy way to navigate back to the source sheet
- the drill creates a new sheet each time.
The drill function of Excel actually does this: if you ask the details of the TotalSales measure, Excel/PowerPivot filters the table the measure is associated with (in this case FactInternetSales), and shows all columns of this table. So, if you decided to associate TotalSales with DimCustomer and drill for details then PowerPivot will return records from DimCustomer. Which of course is of no use. Likewise, if you decide to store all your measures in a dummy table – as many developers do – then the drill will return no records at all!
How does PowerDrill solve these issues?
After installing and activating PowerDrill simply doubleclick on a value in the pivottable (see above) and the magic happens: PowerDrill automatically creates a new sheet “DrillDetails” and shows the details like so:
Compared to the classic view by Excel the improvements are:
- easy to read header names
- easy to navigate back to the source sheet (click the left-arrow)
- the drill details always show on the same sheet DrillDetails
- PowerDrill shows the number of returned rows
- PowerDrill shows the filter conditions for this drill.
However, PowerDrill still not returns the columns we want to see. What we do want is something like this:
In this custom drill by PowerDrill the column EnglishProductname comes from the related table DimProducts, and Lastname and Emailaddress are from related table DimCustomers. Isn’t this nice? I describe later how to configure this custom drill.
Limitations
PowerDrill has a few limitations.
Limitation 1
After a double-click you can run into this message:
You wil see this message when you connect a slicer to the pivottable, select two or more (but not all) options, and do not add a report filter for this field. Under these specific or-like conditions Excel won’t return a MDX string for the double-clicked pivotcell. Since PowerDrill needs this MDX string it is not able to show the Details, and gives control back to Excel to handle your drill request. Excel is able to show the details but unfortunately in classic view.
Note: in Excel 2013 and before, the power pivot engine wasn’t able to handle a or-condition at all and gave an error. In Excel 2016 this issue was resolved: Excel now returns the details correctly (but as stated above, still does not return a MDX string of the pivotcell that was double-clicked).
Limitation 2
PowerDrill only intercepts a double-click. If you select the Show Details command in the pivottable context menu (right-click in the pivottable) then Excel will return the results in “classic” view. Consider this as a feature: you can always compare the PowerDrill results with the results Excel would give.
How to configure a custom drill
Step 1: Add the DrillReturnColumns sheet
To create a custom drill you need to add a special sheet named DrillReturnColumns. Use the PowerDrill Setup button in the Addd-ins menu of the ribbon to add this sheet:
After choosing option Add sheet DrillReturnColumns PowerDrill adds the sheet with an empty table.
Step 2: define your own set of columns
In the figure below you see the ReturnColumns table as it was used in the custom drill I showed above:
That’s it! All you need to do is list the return columns for a custom drill in the second column of this table. To find the right syntax of the column names you can optionally choose the PowerDrill Setup-command List all possible return columns. PowerDrill shows the list on the DrillDetails worksheet like so:
Simply copy and paste the column names of your choice to the DrillReturnColumns table.
Note: make sure that you chose only columns that are related in the context of the drilled measures. Otherwise it can result in wrong or unpredictable results.
After completing the DrillReturnColumns table you can hide this sheet.
Step 3: Different columns for different measures
In the example above we left the column Measure in the DrillReturnColumns table empty. In that case PowerDrill returns the same set of columns for every drill on every measure.
Now suppose you have another measure CountOfSales which simply counts the records in the FactInternetSales table. And suppose you want to see a different set of columns when you drill into CountOfSales. The solution for this is straightforward:
In this case a drill on CountOfSales returns its own set of columns. All other measures will return the default set of columns for which Measure is empty.
Max Rows
Initially the number of records returned is limited to 1.000, as is shown in the title of the DrillDetails sheet. In PowerDrill’s Setup menu you find the option Set max rows to modify this limit:
Note: Max Rows is an internal PowerDrill property. In Show Details Excel still uses the property MaxDrillthroughRecords of the workbook connection to the model.
How it works technically
After a double-click PowerDrill first verifies the following conditions:
- the workbook must have a sheet named DrillDetails; if it doesn’t exist PowerDrill will create the sheet
- the active cell must be a cell in a pivottable
- the pivottable must be connected to a datamodel
- the active cell must be of type Value
- the active cell may not be a Grand Total cell when a pivotfield is filtered in the row or columns area
- the MDX-string for the active PivotCell must not be empty.
If one of these conditions is not met then PowerDrill will return control to Excel to handle the request in classic view. If all conditions are met then PowerDrill builts a MDX Drillthrough command and assigns this command to the Modelconnection of the DrillDetails table and refreshes the connection.
Updates
Version 1.0, December 2019:
- initial release
Version 1.01, October 2020:
- improved drill handling
- improved display of filter conditions in sheet DrillDetails.
Version 2.0, October 2020:
- creates the DrillDetails sheet automatically if it doesn’t exist
- works for pivottables connected to a dataset in Power BI Service or Power BI Desktop (Analyze in Excel by SQLBI)
- improved support for drills in Subtotals and Grand Totals
Version 2.01, November 2020:
Improved handling of drill on Totals range when rows or columns are filtered. See also The Grand Totals range of a pivottable in Excel.
Link for download
Enter your e-mail below and you will receive an email with a download link to the zip-file with PowerDrill.xlam. It’s free!
We respect your privacy, and will use your e-mail only for communication related to this download.
this is very helpful