The Home Ribbon

The Home ribbon is used for all core data loading and manipulation such as filtering and sorting. The buttons that it contains are shown in Figure 9-2 and explained in Table 9-1.

The Design Ribbon

The Design ribbon lets you present the data on screen as well as manage the various properties of the data tables that are joined together. The buttons that it contains are shown in Figure 9-3 and explained in Table 9-2, which follows the image.

The Advanced Ribbon

The Advanced ribbon lets you prepare the data for effective visualization in Power View and other tools. It might not, however, be displayed initially in your environment. Nonetheless, it is something that we will need to use eventually to prepare the data set for visualization. So, if it is not displayed,

1. Click the File menu to the left of the Home tab.
2. Select Switch To Advanced Mode.

The Advanced ribbon will appear to the right of the Design ribbon.

The buttons that it contains are shown in Figure 9-4 and described in Table 9-3.

Preview and Filter Options

The preview and filter options, which are available in the Preview Selected Table pane of the Data Import Wizard, are not limited to merely including or excluding a selection of values from the data source. You can also either define ranges of numeric values to allow into PowerPivot or set the ranges of values that you wish to exclude. Similarly, you can define text ranges to include or exclude.

These options are available in the Text Filters/Number Filters or Date Filters popup that is displayed when you click on the filter popup for a column when importing data. You can see this in Figure 9-12.

These options are identical to those that you use when filtering data in PowerPivot itself. As I explain them in the “Filtering Data in PowerPivot Tables” section later in this chapter, I will not explain them here, but I suggest that you skip ahead to this section if you need these options when you are importing data.

Writing Queries to Select Data

If you have a basic understanding of relational databases—or better, of SQL (the database language used to query SQL Server)—then you can write your own queries to select the data that is imported. A SQL query will select both the rows and the columns of data that will be imported. It can also join tables to select data from several source tables as a single output table.

Fortunately, this does not mean that you have to be a SQL guru to write potentially complex SQL, since the Table Import Wizard has a design pane that will help you create a valid SQL query. Alternatively, if you have existing SQL queries stored as files, you can import them directly to save both time and eventual errors.

To write or design a SQL query that will select the columns and rows of data that you want to use in a PowerPivot table

1. At step 8 in the “Loading Data from SQL Server” section earlier, when the Choose How To Import Data pane of the Table Import Wizard is visible, select Write A Query That Will Specify The Data To Import.
2. Click Next. The Specify A SQL Query pane of the Table Import Wizard will be displayed.
3. If you are a SQL guru, then type or paste in your SQL. If not, then click Design. The Table Import Wizard Design dialog will be displayed (this is explained in more detail in the following pages).
4. Expand the table or view containing the fields that you wish to import in the left-hand pane of the dialog.
5. Select the fields that interest you. The order in which they are selected will be the column order in the PowerPivot table.
6. Click OK. The wizard will write the SQL to filter the data and display it in the dialog. You can then tweak it if you wish.
7. Click Finish to import the data.

Finally you may see a few options when using the Table Import Wizard that I have not yet explained. These are outlined in Table 9-4.

Filtering Data Using the Table Import Wizard Design Dialog

The Table Import Wizard Design dialog is a surprisingly complete and powerful tool for writing fairly complex SQL that will then be used to import data. Although this dialog is fairly intuitive, it is nonetheless a tool that requires a little explanation if you are to use it efficiently.

The Table Import Wizard Design dialog of the Table Import Wizard contains four main parts:

• The Database View pane (on the left) where you can see all the tables and views that you are authorized to access in the database. You can expand the tables and/or views that you want to use here and select and deselect all the fields you want to import. Remember that if you are using data from more than one table, you will have to specify relationships between all the tables and/or views used.
• The Selected Fields pane (the top pane on the right). This pane lists the fields that you have selected and allows you to
  • Change the field order using the up and down icons.
  • Remove a field from the list.
  • Group and aggregate data.

• The Relationships pane (the middle pane on the right). This pane is hidden by default, and you will need to expand it if you are adding or modifying relationships between tables and/or views. it allows you to
  • Detect relationships that already exist in the source database between any of the tables and/or views from which you have selected fields.
  • Add new relationships manually by specifying the fields that make up the relationship.
  • Delete relationships (in the query that you are creating, not in the source database).
  • Change the order of relationships.

• The applied filters pane, which lets you
  • Add filters to subset the data.
  • Delete filters.
  • Modify filters.

These key elements are shown in Figure 9-13.

So you can see how the Design dialog can work, I suggest a simple example where we will select data from a few fields in two tables in the source database (Clients and Countries). We will join the tables, and then filter to select data only for car dealers.

1. From the PowerPivot screen, choose Existing Connections Open Write A Query That Will Specify The Data To Import Next Design. This will display the Design dialog.
2. In the Database View pane (on the left) expand the source tables Clients and Countries.
3. Select the ClientName and ClientType fields from the Clients table and select CountryName_EN from the Countries table. You do this by selecting the check box for each required field in the Database View pane. The fields will appear on the right in the upper (Selected Fields) pane.
4. Change the field order. In this example I want the Country Name to be just after the Client Name. Consequently you need to click on the CountryName_EN field in the Selected Fields and click on the Move Up arrow.
5. Open the Relationships pane by clicking on the Expand Pane icon (the double downward-facing chevron). Click AutoDetect. A new relationship will be added to the Relationships pane. PowerPivot knows that these two tables are linked on the CountryID field in each table.
6. Click the Add filter icon (the funnel) above the Applied Filters pane. A new filter will be added showing the first field in the Selected Fields pane.
7. Click on the Field name that is shown. A popup of all the available fields in the tables used in the query appears, as shown in Figure 9-14.

   

   Figure 9-14. Choosing a field to filter on in the Table Import Wizard Design dialog

8. Select the ClientSize field.
9. Click on the operator in the second column of the filter and select the Is option.
10. Click in the third column, enter the text Large and click the tiny plus symbol. The filter will read ClientSize Is Large.
11. Click OK. The SQL for the query will be displayed in the wizard.
12. Click in the (top) Friendly Name Query box and change the name from Query to something more meaningful, such as ListOfDealers, as shown in Figure 9-15. This will become the table name, saving you from having to rename it later.

    

    Figure 9-15. The final SQL created by the Table Import Wizard Design dialog

13. Click Finish. The query will run and the data will be imported.

There are only a few points to note at this juncture:

• You can create multiple filters. If you do, then all criteria in all the filters will be used simultaneously against the source data to produce (quite possibly) a substantially reduced data set.
• If you choose to define your own relationships between tables, you will need to know which fields to use to create the joins.
• If you are using the Like operator in a filter, then you will need to add a % symbol before and after the text that you are searching for if you want to perform a wildcard search.
• You can always use the designer to create the initial SQL statement and manually tweak it later in the Specify A SQL Query pane of the designer.

List of Tables or Write a Query?

You may be wondering why there are two paths to choose from in the Table Import Wizard in the Choose How To Import The Data pane (which you can see in Figure 9-7 earlier in this chapter). Apart from the reason that we saw immediately in the preceding section—that the Write A Query That Will Specify The Data To Import option allows you to paste in an existing SQL query that you have developed in (say) SQL Server Management Studio—there are other reasons. The differences are outlined in Table 9-5.

Copying and Pasting Data from an Excel Workbook

As long as the source data that you wish to add to PowerPivot is not too voluminous (which you will only discover with experience, and which depends, in any case, on the resources of the PC that you are using), you can simply copy and paste data into PowerPivot from Excel like this:

1. Go to the Excel worksheet containing the data that you wish to copy. In this example it is the sheet named ReferenceData in the Excel file PowerPivotSample.xlsx.
2. Select the data to add to PowerPivot (A1:B10—the table of colors in this example, except for the last line) and copy the table.
3. Switch to the Excel workbook in which you are building a PowerPivot data set (if in another workbook) and switch to PowerPivot. Click Paste in the Home ribbon. The Paste Preview dialog will appear.
4. Enter a table name (Colours in this example).
5. If the data that you copied contains column headers, ensure that the Use First Row As Column Headers. check box is selected. If the data table does not contain column headers, then you can leave this unchecked. The dialog will look like Figure 9-17.

   

   Figure 9-17. Pasting data from Excel

6. Click OK.

The data is copied into PowerPivot and becomes a table with the name you entered.

Appending Data from Excel

If you have copied data from Excel, then the resulting table in PowerPivot is completely static. So if you want to add further data, you need to do it this way:

1. In the source file (PowerPivotSample.xlsx if you are following this example) select the data to add (ReferenceData!A11:B11). Copy these cells.
2. Switch to PowerPivot, select the Colours table, and click Paste Append in the Home ribbon. The Paste Preview dialog will appear, as shown in Figure 9-18.

   

   Figure 9-18. The Paste Preview dialog

3. Ensure that the format of the copied data is identical to the structure of the destination table, then click OK.

The data that you copied is added to the PowerPivot table at the end of the existing data. If you copied data that includes header rows, then ensure that these do not cause a problem by checking the Exclude First Row Of Copied Data check box.

Updating a Copied Table

In some cases it is easier to replace the entire table in PowerPivot with a new table from Excel. In this case you have to

1. Select the source data in Excel.
2. Switch to PowerPivot and select the destination table.
3. Click Paste Replace in the Home ribbon. The Paste Preview dialog will appear, and you must, once again, check that the source and destination formats are identical.

Importing Data from an Excel Workbook

If you are fortunate enough to have a table of potential source data in the same Excel file as the one where you are creating your PowerPivot data set, then you can import this table directly into PowerPivot without having to copy and paste. Not only is this operation easier to accomplish, but it places less strain on the resources available on the host PC, and it also makes updating and appending data easier.

If you look at the sample file PowerPivotSample.xlsx, you will see a large table of dates in the ReferenceData worksheet. We will now import this table directly into PowerPivot. In case you were wondering, we need a table of dates to apply what PowerPivot calls “Time Intelligence” to our data, but we’ll discuss this more later on in Chapter 10.

1. Select the table of data to import in Excel. In this example it is the range E1:Z1462 in the ReferenceData worksheet of the PowerPivotSample.xlsx file.
2. Click PowerPivot to activate the PowerPivot ribbon in Excel.
3. Click the Add To Data Model button.

The data is added as a new table to PowerPivot. The table is named Table n, but we will see how to change this very shortly.

In the meantime, adding a table from Excel has caused a new ribbon to become visible. This is the Linked Tables ribbon, and its various options (which are shown in Figure 9-19 and describe in Table 9-7) allow you to handle various aspects of linked table data.

Refreshing a Single Table Connected to an External Data Source

Here is how to refresh a single table. I will use the SalesData table that came from an SQL Server database.

1. Select the table whose data you wish to refresh (SalesData, in this example).
2. In the Home ribbon, click the Refresh button. The refresh process starts, and the Data Refresh dialog appears as shown in Figure 9-20.

   

   Figure 9-20. The Data Refresh dialog

3. Click Close, assuming that the refresh was flagged as successful.

A data refresh will replace all the data in the table with the latest version from the data source. After the refresh operation, the data will, once again, be a copy of the source data that you chose; that is, it will use the selection of columns and any filters that you set up when you loaded the data initially.

Refreshing All the Tables in the Data Set Connected to an External Data Source

This technique is extremely useful when you know that a set of linked tables in an external source has changed, and that consequently they all need to be updated in PowerPivot together for the data to remain coherent and valid. To do this

1. In the Home ribbon, click Existing Connections.
2. The Existing Connections dialog appears and lists all the current connections to external data sources.
3. Click on the connection that you want to update. This is shown in Figure 9-21 using the SQLServer Adam02 CarSalesData connection.

   

   Figure 9-21. The Existing Connections dialog

4. Click Refresh. The Data Refresh dialog will appear (as seen previously in Figure 9-20) and the data will be refreshed. If you are dealing with many tables, or large amounts of data, then this could take a few minutes.
5. Click Close to close the Data Refresh dialog.
6. Click Close to close the Existing Connections dialog.

All the tables where the data originally came from for this data source will be refreshed.

Refreshing All the Tables in the Data Set Connected to an External Data Source

In the case of complex data sets or outdated data, you may well have no choice but to refresh all the tables at once. To do this

1. In the Home ribbon, click the popup menu for the Refresh button and select Refresh All.
2. The Data Refresh dialog appears and lists all the tables whose data is being updated from the data sources.

Refreshing a Single Table Connected to an Excel Worksheet

Here is how to refresh a single table connected to an excel worksheet:

1. Select the table whose data you wish to refresh (DateTable in this example).
2. Ensure that the Linked Table ribbon is activated.
3. Click the Update Selected button.

The data will be refreshed from the source worksheet. Here, too, the current PowerPivot data will be replaced by the data in the source.

Refreshing All the Tables in the Data Set Connected to Excel Worksheets

In the case of complex data sets or outdated data you may well have no choice but to refresh all the tables at once. To do this

1. Click inside any table that is linked to Excel data (remember that there is a tiny chain icon to the left of the table name to indicate that this is a linked Excel table).
2. Ensure that the Linked Table ribbon is activated.
3. Click the Update All button.

Renaming Tables

Suppose that you wish to rename a table, such as the table of dates that you imported from Excel earlier. These are the steps to follow:

1. Right-Click on the table name tab at the bottom of the PowerPivot window.
2. Select Rename. The current name will be highlighted in the tab.
3. Enter the new name, or modify the existing name.
4. Press Enter.

Deleting a Table

Deleting a table is virtually identical to the process of renaming one—you just right-click on the table name tab and select Delete instead of Rename. As this is a potentially far-reaching operation, PowerPivot will demand confirmation.

Moving a Table

Moving a table is virtually identical to the way in which you move worksheets in an Excel workbook (a process that I am sure that you already know by heart).

1. Click on the table name at the bottom of the PowerPivot window.
2. Drag the table left or right to its new position.

A small black triangle indicates where the table will move. This is shown in Figure 9-22.

The other core table management options are described in Table 9-12.

Selecting a Column from the List of Available Column Names

If you want to leap straight to a column of data (and presuming you know which column contains the data that interests you), all you have to do is

1. Click on the popup (the downward-facing triangle) for the Field List (on the left, immediately above the table grid). An alphabetical list of the fields in the table appears, as shown in Figure 9-23.

   

   Figure 9-23. The list of columns in a table

2. Click on the name of the field that contains the data that you want to study.

The cursor will jump to the chosen column in the table. The cursor will remain on the same record it was on before you selected this column.

Searching for Metadata

In the case where you have a pretty good idea of the column name (or table name) that you are looking for, you can get PowerPivot to locate it for you.

1. In the Home ribbon, click Find. The Find Metadata dialog will be displayed.
2. Enter all or part of the field name that you want to locate. I suggest entering the word InvoiceDate (without quotes) if you are using the sample data. The dialog will look like Figure 9-24.

   

   Figure 9-24. The Find Metadata dialog

3. Click Find Next (or Find Previous) until you have located the table and/or column that you are looking for.
4. Click Close.

In this example I was looking for column titles, but you can look for any element that PowerPivot contains, whether it is a table, a column, a KPI, or a hierarchy (these latter two are explained in Chapter 11), for instance. Also, if the element is hidden (more of this in the next chapter) you can check the Show Hidden Fields While Finding Metadata check box to have PowerPivot look for hidden elements too.

Also—even if I did mention it in passing in step 2—it is worth noting that you do not have to enter the complete title of a field (or any other valid metadata item). Part of the name will suffice.

Renaming a Column

Renaming a column is pretty straightforward. All you have to do is

1. Right-click on the title of the column that you wish to rename. In this case it will be CountryName_EN. The column will be selected and the context menu will appear. This is shown in Figure 9-25.

   

   Figure 9-25. The PowerPivot context menu

2. Select Rename Column from the context menu. The current column name will be highlighted.
3. Type in the new name for the column (CountryName) and press Enter.

And that is it; your column has been renamed. You cannot, however, use the name of an existing column in the same table. If you try, PowerPivot will name the column InvoiceDate2, for example.

Although renaming a column may seem trivial, it can be important. Consider other users first; they need columns to have instantly understandable names that mean something to them. Then there is the Power BI Q&A natural language feature. This will only work well if your columns have the sort of names that are used in the queries—or ones that are recognizable synonyms. Finally, you cannot rename columns in Power View, so you really need to give your columns the names that you are happy seeing in the final output.

Deleting Columns

Deleting a column is equally easy. You will probably find yourself doing this when you have either brought in a column that you did not mean to import, or you find that you no longer need a column. So, to delete a column

1. Right-click on the title of the column that you wish to delete. The column will be selected and the context menu will appear.
2. Select Delete Columns from the context menu. Unless the column is empty, the confirmation dialog will appear as shown in Figure 9-26.

   

   Figure 9-26. Deleting a column

3. Click OK. The column will be deleted from the table.

Deleting unused columns is good practice, as this way you will

• Reduce the memory required for the data set.
• Speed up use of the data in tools like Power View.
• Reduce the size of the Excel file.

Moving Columns

When you first load data from Excel or SQL Server in this chapter (or from the sources that you are using in your work with PowerPivot) you will noticed that the structure of the tables in PowerPivot reflects exactly the structure of the source tables. All columns appear in PowerPivot in the order in which they appear in the source. Now, as we saw earlier, if you are specifying the data to load using a query or the Table Import Wizard Design dialog, then you can set the order of the columns as you want them in PowerPivot. However, if you prefer to work faster and import a group of tables at once, you have to accept the table structure as is.

However, all is not lost if you want to reorganize the column structure. Doing this can be useful from a data analysis perspective, since it allows you to place columns in an order that makes sense to you. So suppose that you want to move the CostPrice column to the left so that it is now beside, and to the right, of the SalePrice column. This is what you have to do:

1. Click on the column you wish to move (CostPrice). The column will be selected. Ensure that you release the mouse button.
2. Move the mouse pointer over the column title. The cursor becomes a four-headed arrow.
3. Drag the column left or right to its new position. A slim vertical bar will appear to indicate where the column will be moved between existing columns, and you will see a “shadow” of the column header under the mouse pointer.

Figure 9-27 shows how this is done.

Setting Column Widths

One final thing that you may want to do to make your data more readable—and consequently easier to manipulate—is to adjust the column width. I realize that as an Excel user you may find this old hat, but in the interests of completeness, here is how you do it:

1. Place the mouse pointer over the right-hand limit of the column title in the column whose width you want to alter. The cursor will become a two-headed arrow.
2. Drag the cursor left or right.

As is the case with Excel, you can select several adjacent columns before widening (or narrowing) one of them to set them all to the width of the column that you are adjusting. You can also double-click on the right-hand limit of the column title in the column whose width you want to alter to have PowerPivot set the width to that of the longest element in the column. Finally you can right-click on a column title and select Column Width. . . to display the Column Width dialog and set the width exactly.

Freezing Columns

When taking an in-depth look at your data you are likely to want to set row titles to the left of the window, just as PowerPivot sets the column titles at the top of the data. There are essentially two ways to do this, depending on whether you wish to use a single column as the row titles or a group of columns.

If all you want to do is to use a single column for the fixed row titles, then all you have to do is

1. Right-click on the title of the column that you wish to use. The column will be selected and the context menu will appear.
2. Select Freeze Columns from the context menu and the selected column will be moved to the left of the table and its contents will be fixed as row titles.

You can now scroll right in the PowerPivot window, and the leftmost column will always remain visible. You will see a slightly thicker column border to the right of the frozen column as a visual indication that the row titles have been set.

If you want to use several columns as row titles

1. Move the columns that you wish to use to the left of the PowerPivot window as a contiguous block.
2. Select the titles of these columns.
3. Right-click on the title of one of the columns and choose Freeze Columns.

You can now scroll right in the PowerPivot window and the chosen columns will always remain visible.

To unfreeze all the row titles all you have to do is right-click on any column title—whether it is a frozen row title or an ordinary column—and select Unfreeze All Columns.

Other Currency Formats

By default PowerPivot will apply the currency format that has been set for the PC as a default. If you wish to use another format, then you can do it in this way:

1. Click on the popup (the downward-facing triangle) to the right of the currency format icon. This will display a list of available formats.
2. Select the currency symbol that you want, or click on More Formats to display all the available currency formats, as shown in Figure 9-28, then click OK.

   

   Figure 9-28. The currency format dialog

Clearing Filters

As well as applying filters, you will probably need to remove filters from a table. You can do this either for a specific filter, or by clearing all the active filters on a table.

To clear an individual filter:

1. Click on the popup icon to the right of the column name. In this example, it is for the Marque column in the SalesData table.
2. In the filter popup, click Clear Filter From “Marque.”

The filter is removed, and any data that was hidden by the filter reappears. You will see that the row count indicator at the bottom left of the PowerPivot window has changed to show a larger number of records.

If you want to remove all filters, simply click on the Clear All Filters button in the Home ribbon. All filters will be removed and the entire data set will reappear.

Custom Filters—Text

Rather than choosing lists of items to include or exclude (as we just did), you can also apply more advanced filters to PowerPivot data tables. As with so much in PowerPivot, this is probably best understood if you see an example, so:

1. Click on the popup icon to the right of the column name (I will use the Make column).
2. In the filter popup, click on the popup triangle for Text Filters.
3. Click Equals The Custom Filter dialog appears.
4. Leave Is Equal To on the left and enter Jaguar) in the upper text box on the right. The dialog should look like Figure 9-31.

   

   Figure 9-31. The Filter popup

5. Click OK.

The data will be filtered to show only records where the make is Jaguar.

The principal advantage of using advanced filters with text columns is that it can simply be faster to set a filter by typing it in. This also allows you to enter up to two filters by selecting the And option in the Custom Filter dialog and setting the second row in the dialog to filter on another element.

Custom Filters—Numeric

Advanced filters become more useful when applied to numeric data. This is principally because they let you define a range of data—you assign a lower and upper threshold to subset the data if you prefer. To see this in action, let’s filter the data for the SalesData table to show only sale prices between £50,000.00 and £100,000.00.

1. Click on the popup icon to the right of the column name (using the SalePrice column here).
2. In the filter popup, click on the popup triangle for Number Filters.
3. Click Greater Than Or Equal To. The Custom Filter dialog appears.
4. Leave Greater Than Or Equal To on the left and enter 50000 in the upper text box on the right.
5. Make sure that And is selected in the center of the Custom Filter dialog.
6. Select Less Than Or Equal To from the lower of the two popups.
7. Enter 100000) in the lower text box on the right. The Custom Filter dialog should look like Figure 9-32.

   

   Figure 9-32. The Custom Filter dialog for a range of numbers

8. Click OK.

The data will be filtered to show only records where the sale price is between £50,000.00 and £100,000.00.

This is not, of course, the only way in which numbers can be filtered. You can look for

• Numbers above a certain limit (by setting the lower threshold only to Greater Than or Greater Than Or Equal To)
• Numbers below a certain limit (by setting the lower threshold only to Less Than or Less Than Or Equal To)
• Numbers outside a specified range, by inversing the use of greater than and less than in steps 3 and 6 in the preceding exercise
• Numbers not equal to a certain value by using the Not Equal To option.

To modify an existing custom filter, all you have to do is click on the popup icon to the right of the column name, click on the popup triangle for Number Filters, and select Custom Filter. The filter that you last specified will reappear and you can tweak it as you see fit.

Custom Filters—Date

Filtering by date is very similar to filtering by numbers. You can select date ranges or all elements where a date is greater or lesser than a certain date. The available options (which include a superb range of instant presets) are outlined in Table 9-14.