Power Pivot Principles: Menu Options

Welcome back to our Power Pivot blog series. Today we consider some of the various menu options in the Power Pivot window: main course or entrée...?

Overview

Now that we have a good understanding on what Power Pivot is and how to import data, let’s run through the different menu options available in Power Pivot. Understanding the menu options will provide a basis of how to get the best out of Power Pivot. There are three menu tabs on the Power Pivot Ribbon: ‘Home’, ‘Design’ and ‘Advanced’. In this blog, let’s go consider the ‘Home’ tab:

Power Pivot Menu Options: Home

There are multiple groups that make up the ‘Home’ tab:

Clipboard

This section allows data to be copied from our Power Pivot data model and pasted outside of the data model. Data can also be copied from Excel and pasted as a table in the Power Pivot data model.

The ‘Paste Append’ option allows us to paste new data at the foot of existing data and ‘Paste Replace’ allows us to replace all existing data. Please note that when using these options, the data model which we are pasting the data from needs to be in the same structure as the Power Pivot data model for it to copy over correctly.

Get External Data

This is how data is imported from external sources into Power Pivot. There are many different options to import data. Existing connections can also be accessed from this section.

Refresh

If our Power Pivot model is connected to data from another source and that source data is updated, this is where the Power Pivot data will be refreshed from. We can either refresh a specific table or the entire data set.

PivotTable

This option gives us the ability to analyse data in a PivotTable or chart. There are different options we can select – either a PivotTable or chart on their own or together or even multiple tables together.

Formatting

The formatting section allows us to change the data type: decimal number, text, date, currency etc. We can also decrease / increase the number of decimal places on a number. How we format the data in the Power Pivot window is how the data will be displayed in the PivotTable or chart. I spoke more about the difference between Data Types and Formatting here.

Sort and Filter

The sort and filter options allow us to sort our data. Depending on what type of data format the column is showing will determine how we can sort the data.

• If the data is in date format, then the option would be to sort our data from ‘oldest to newest’ or ‘newest to oldest’
• If the format is numeric, the sort option will show ‘smallest to largest’ or ‘largest to smallest’
• The ‘Clear All Filters’ option allows us to clear any filters that are currently being used to view the data
• The ‘Sort by Column’ option allows us to sort a column based on another column. For example, to sort the month name column in the correct order we could sort it by the month number.

Find

The ‘Find’ option allows us to find metadata like fields or field names. If we need to search for specific data in our data model, we would use the filter drop-down arrows on the columns.

AutoSum

The ‘AutoSum’ feature allows us to create calculated fields based on the sum of the selected column and display it below the column. There’s other ways to do this though – but more on that in a later article…

Create KPI

Allows us to convert calculated fields into a Key Performance Indicator (KPI).

View

The ‘View’ section allows us to view our data in different ways.

• The ‘Data View’ shows all of the columns of data
• The ‘Diagram View’ shows the list of our tables and shows the relationships between the tables
• The ‘Show Hidden’ option allows us to show objects in the model that have been hidden from the client tools
• The ‘Calculation Area’ is used for creating, editing and managing calculated fields and KPIs within the model.

Feel free to play around with these options in Power Pivot, to get a better feel of the software suite.

Stay tuned for our next post on Power Pivot. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.