Hidden features of Analyze in Excel – Part 2 – Give me all the data

In the previous blog post we discussed how to define the details of the drill through feature of a Pivot table.

Show Details – default results

As we learned how to define the results of the Show Details capability, we encounter another potential issue. As you can see in the image below the cell selected represented almost 2.000 rows, but by default you only get the first 1.000 rows. This way this feature it not that useful.

Default Show Details functionality – 1.000 rows

In an ideal world you would expect to see all the data and if you miss the first row you might believe you actual have it. This can cause a lot of confusion.

Fortunately, there is a solution to get all the details.

Connection Properties – OLAP Drill Through

If you go to the properties of the connection, you will see an option that most people ignored so far.

It is called Maximum number of records retrieve.

Connection properties

You can increase this number to anything that an excel sheet can cope with. Of course, getting more data require an efficient data model, good internet connection and a suitable hardware spec.

Show Details made more useful

Together with the Details Rows Expression and increasing number of rows retrieved in Show Details, we can increase the value of these standard Excel functions.

Modified Show Details functionality – 10.000 rows

Considerations

  • Because this setting is in Excel it cannot be set in the Power BI model.
  • You need to open the file in the desktop version of Excel you cannot use or set this feature in the Excel Online.
  • It is possible to create a connection file to save the details, but it does not save this setting. This will be useful in the next part of this series. For now, you need to save the excel file created from Analyze in Excel with this setting modified to a shared drive for the excel users to use.
  • Currently searching for a solution to change this setting globally for all excel files so it is not a manual effort every time. If you know one, please share it with me.
Export Connections into an .ODS file

Summary

This and the previous post not just ensure that the Excel users do not feel any negative impact by using a Power BI dataset but instead adds value to them. In the next blog I will show how you can reduce distractions and complexity when working with a Power BI dataset in Excel.

Hope you like this blog post, see you in the next one.

Please follow and like us:

About the author

Mihaly Kavasi

Data-driven Decision-Making Enthusiast.
Power BI Expert and Trainer. Helping organizations and communities to use data effectively.
Big fan of RE and Electric cars.
FTRSA, MCSE, MCT
Working @ Avanade

View all posts