Overview of Tables and Columns

After you have added data into the PowerPivot window, you can work with the tables of data by adding new columns of data, defining calculations that extend the data, changing the data type of columns, or by rearranging the table design for easier viewing.

This section describes how to work with tables and columns in the PowerPivot window.

Create a New Table

In the PowerPivot window, a new tab is created automatically for you whenever data is pasted or imported from another data source. You do not create new, empty PowerPivot tables directly. Each tab contains one table of data, which can include the following:

  • A single table or view from a relational database, or from other non-relational sources, such as an Analysis Services cube.

  • Data that is linked to tables in an Excel workbook.

  • Data that has been copied and pasted from an Excel workbook or another source, such as Microsoft Word. The data that you paste from the Clipboard must be in HTML format.

  • A tabular set of data imported from a feed or text file.

For more information about the types of data that you can add to a PowerPivot workbook, see Add and Maintain Data in PowerPivot.

Combining Multiple Data Sources

When you import data, each table or view, sheet, or file of data is added as a table to its own tab in the PowerPivot window. You typically add data from various sources onto separate tabs, but you can combine data in a single table by using Paste and Paste Append. For more information, see Copy and Paste Data to PowerPivot.

After you have added the data that you need, you can create relationships between the tables, look up or reference related values in other tables, or create derived values by adding new calculated columns. For more information about using calculations to combine data values or look up related values, see Create a Calculated Column. When you import data, PowerPivot will also detect and create relationships automatically for you. For more information about creating relationships, see Relationships Between Tables

Differences Between a PowerPivot Table and an Excel Worksheet

  • You cannot add a row to a PowerPivot table by directly typing in a new row like you can in an Excel worksheet. But you can add rows by using Paste Append and by refreshing data.

  • Data in an Excel worksheet is often variable and ragged: that is, one row might contain numeric data, and the next row might contain a graphic or string of text. In contrast, PowerPivot data is more like that in a relational database, in which each row has the same number of columns and most columns contain data.

  • The PowerPivot table store supports most data types used by Microsoft Excel, and by other relational data stores. Where conversion is necessary to perform operations such as multiplication or concatenation, PowerPivot performs the conversion implicitly. For information about data type conversion, and about the data types supported in formulas, see Data Types Supported in PowerPivot Workbooks.

Delete a Table

You can delete tables that you no longer need. Deleting a table in the PowerPivot workbook does not affect the original source data, only the data that you imported and were working with in PowerPivot. You cannot undo the deletion of a table.

To delete a table

  1. Right-click the tab at the bottom of the PowerPivot window for the table that you want to delete.

  2. Click Delete.

Considerations when Deleting Tables

  • When you delete a table, the entire tab that the table was on is deleted.

  • If any measures were associated with that table, the definition of the measure will also be deleted.

  • If you created any calculated columns using that table, columns in that table are also deleted; any calculated columns in other tables that use columns from the deleted table will display an error.

Rename a Table or Column

You can change the name of a table during the import process by typing a Friendly Name in the Select Tables and Views page of the Table Import Wizard. You can also change table and column names if you import data by specifying a query on the Specify a SQL Query page of the Table Import Wizard.

After you have added the data to the workbook, the name (or title) of a table appears on the table tab, at the bottom of the PowerPivot window. You can change the name of your table to give it a more appropriate name. You can also rename a column after the data has been added to the workbook. This option is especially important when you have imported data from multiple sources, and want to ensure that columns in different tables have names that are easy to distinguish.

Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To rename a table

  1. In the PowerPivot window, right-click the tab that contains the table that you want to rename, and then click Rename.

  2. Type the new name.

    Note

    You can edit other properties of the table, including the connection information and column mappings, by using the Edit Table Properties dialog box. However, you cannot change the name in that dialog box.

To rename a column

  1. In the PowerPivot window, double-click the header of the column that you want to rename, or right-click the header and select Rename Column from the context menu.

  2. Type the new name.

Naming Requirements for Columns and Tables

The following words and characters cannot be used in the name of a table or column:

  • Leading or trailing spaces

  • Control characters

  • The following characters (which are not valid in the names of Analysis Services objects): .,;':/\*|?&%$!+=()[]{}<>

  • Analysis Services reserved keywords, including Multidimensional Expressions (MDX) and Data Mining Extensions (DMX) function names and operators.

Effect of Renaming on Existing Tables, Columns, and Calculations

Whenever you change the name of a table, you change the name of the underlying table object, which may contain multiple columns or measures. Therefore, any columns that are in the table, and any relationships that use the table, must be updated to use the new name in their definitions. This update happens automatically in some cases. Measures are not updated automatically.

Moreover, any calculations that use the renamed table, or that use columns from the renamed table, must also be updated, and the data derived from those calculations must be refreshed and recalculated. Depending on how many tables and calculations are affected, this can take some time to complete. For more information, see Recalculate Formulas.

Therefore, the best time to rename tables is either during the import process, or before you start to build complex relationships and calculations.

Hide or Freeze Columns

If there are columns that you don't want to display in your table, you can temporarily hide them. Hiding a column gives you more room on the screen to add new columns or to work with only relevant columns of data. You can hide and unhide columns from the ribbon in the PowerPivot window, and from the right-click menu available from each column header. To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific columns in one area by freezing them.

Important

The ability to hide columns is not intended to be used for data security, only to simplify and shorten the list of columns visible in the PivotTable field list and PowerPivot window.

When you hide a column, you have the option to hide the column just while you are working in the PowerPivot window, or to also hide the columns in any associated PivotTables. If you hide all columns, the entire table appears blank in the PowerPivot window. When you hide all columns from a table that is used in a PivotTable, you might receive a message that the data must be refreshed.

Note

In Windows Vista and Windows 7, features in the PowerPivot window are available on a ribbon, which is discussed in this topic. In Windows XP, features are available from a set of menus. If you are using Windows XP and want to see how the menu commands relate to the ribbon commands, see The PowerPivot UI in Windows XP.

To hide an individual column

  1. In the PowerPivot window, select the table that contains the column that you want to hide.

  2. Right-click the column, click Hide Columns, and then click From PowerPivotand PivotTable, From PivotTable, or From PowerPivot.

To hide multiple columns

  1. In the PowerPivot window, select the table that contains the columns that you want to hide.

  2. On the Design tab, in the Columns group, click Hide and Unhide.

  3. In the Hide and Unhide Columns dialog box, locate each column that you want to hide, and then deselect one or both of In PowerPivot and In PivotTable.

  4. Click OK.

To freeze columns

  1. In the PowerPivot window, select the table that contains the columns that you want to freeze.

  2. Select one or more columns to freeze.

  3. On the Home tab, in the View group, click Freeze.

Undo or Redo an Action

PowerPivot for Excel provides the ability to undo many actions that you perform in the PowerPivot window. However, some actions can't be undone. These actions include the following:

  • Saving a file

  • Creating or deleting a table

  • Deleting a column of data (However, you can undo the deletion of a calculated column)

  • Changing data types that might result in the loss of data precision, such as changing a column data type from Double to Integer, or changing from String or Integer to Boolean.

  • Refresh operations

  • Any operations on measures

Performing an operation that cannot be undone clears the undo and redo history; therefore, you lose the opportunity to undo any actions that happened before the preceding operation. If you perform an operation that is meant to remove data and the operation cannot be undone, PowerPivot will ask you to confirm the operation. You do not need to confirm any operations that can be undone, such as deleting a calculated column.

To undo an action

  • In the PowerPivot window, click the Design tab, and in the Edit group, click Undo.

-or-

  • Press CTRL+Z.

Note

If the last action cannot be undone, the Undo command is not available.

To redo an action that you undid

  • In the PowerPivot window, click the Design tab, and in the Edit group, click Redo.

-or-

  • Press CTRL+Y.

Note

If the last action cannot be redone, the Redo command is not available.