Excel Tables
Page 7
Since the VertiPaq engine is built into Excel 2013, the Power Pivot add-in is not required for working with a PivotTable based on a Power Pivot Data Model. However, in order to manage the Data Model, the Power Pivot add-in must be installed.
NOTE
Only certain versions of Excel 2013 support the Power Pivot add-in:
Excel 2013 ProPlus—Available only with an Office 365 subscription
Excel 2013 Professional Plus—Available only through volume licensing
Excel 2013 standalone—Available at retail outlets (for example, Amazon.com)
The Power Pivot add-in is not available in any other editions of Excel 2013.
Once the add-in is installed, you get a new POWERPIVOT ribbon tab, as shown in the next figure.
POWERPIVOT ribbon tab.
Once a Power Pivot workbook is created, you can publish it to a 2010 SharePoint Server or Power BI Site that has Excel Services and a deployment of SQL Server Power Pivot for SharePoint. You can also open a workbook with Power Pivot objects in Excel 2007, but the user cannot interact with any of the PivotTables or PivotCharts created from the Data Model. During any client-side data refresh or Data Model import, all access to external sources goes through the xVelocity engine and runs inside the Excel process. This integrated approach gives the user the ability to create self-service BI solutions from within an Excel session.
NOTE
A 64-bit version of Power Pivot is available and must be used with 64-bit Office. If you’re using 32-bit Office, you must use the 32-bit of Power Pivot (referred to as x86).
Adding a Table to the Data Model
You add a Table to the Data Model by selecting POWERPIVOT | Tables | Add to Data Model. Once a Table is part of the Data Model, it becomes a Linked Table, and you can use it to create relationships and measures in the same way as any other data source.
While a Table is linked to a Data Model, the Linked Table ribbon tab is displayed in the Power Pivot window. The Linked Table tab is a contextual tab, so it appears only when the selected Table is a Linked Table. This ribbon tab has the following controls:
Update All—Updates all linked Tables in the model
Update Selected—Updates only the selected Table in the model
Excel Table—Lists all Linked Tables in the workbook so you can click one to jump to it or type in a name
Go to Excel Table—Goes straight to a Linked Table on the worksheet
Update Mode—Sets whether Linked Tables update automatically or manually
Linked Table ribbon tab in the Power Pivot window.
CAUTION
When you query a Data Model that exposes a table loaded from a Linked Table, the default table name is the same as the Linked Table name, but you can change it. You use the Data Model table name, not the upstream Linked Table name, in any queries directed at the Data Model.
NOTE
Windows XP users do not see a ribbon tab. Instead, they see a command bar control menu with all the same controls. For more information, see http://technet.microsoft.com/en-us/library/gg399147.aspx.
Updating Data
By default, all Linked Tables are set to automatically refresh. This does not mean every modification to a Table triggers an update to the Data Model, especially if the Data Model was created in Excel 2013 and distributed to users who do not have the add-in installed. Some kind of interaction with the Data Model triggers an update. The easiest way to trigger an update is by selecting POWERPIVOT | Tables | Update All.
Another way to refresh the data in the Data Model is to select DATA | Connections | Refresh All. This action refreshes all connections to and from Tables, Data Models, PivotTables, and PivotCharts.
NOTE
A Linked Table is a Table added to a Data Model, and the data flows from the Table to the Data Model; it is not a Table that queries a Data Model.
DAX Table Queries
For years MDX (Multidimensional Expressions) has been the standard query language for extracting data from external databases such as SSAS (SQL Server Analysis Services). DAX (Data Analysis Expressions) replaced MDX when Power Pivot was created. DAX provides much faster access to data and is more tightly integrated into Excel than MDX.
DAX is a query language for Power Pivot and SQL Server Analysis Tabular models. Microsoft designed the DAX language to be easier to use than its predecessor, MDX, by modeling the language after Excel formulas and including dynamic aggregation and relational data functions. It is designed to be easier to understand and learn than other query languages, while leveraging the larger Data Models typically found in Power Pivot Data Models and SSAS. DAX effectively brings powerful data query services to Excels user in a familiar form.
The rest of this section presents an example of using DAX in a Table to query a Power Pivot Data Model and return a set of unique values in a field. This is a common Excel task involved in creating a unique list of data. There are many reasons you might want a unique list of values, such as for summarizing data or for using in-cell drop-down lists with data validation.
CAUTION
You can use DAX queries in a Table only with an OLAP data source (for example, Power Pivot). DAX cannot be used on standalone Excel Tables, text file connections (which can’t return to Tables at all), or web connections (unless the web connection is brought into the Data Model first).
The next figure shows an example of a Table returned from a SQL Server data source. Since this Table is connected to a database, refreshing the Table updates it with the latest data in SQL Server.
SQL Server data source in an Excel Table.
NOTE
The sample data used here is from the AdventureWorks2012 database, provided free by Microsoft, at http://msftdbprodsamples.codeplex.com/releases/view/93587.
When you establish a connection to a SQL Server database and define a query, Excel creates a default DAX query, based on how the query was defined—but only if the data is loaded into a Power Pivot Data Model. To load the data into the Data Model, you need to ensure that the check box Add this data to the Data Model is checked, as shown in the next figure, when you define the connection and query.
Dialog box for importing data.
NOTE
When you build a query to load a Table, the data must be added to the Data Model (that is, loaded into Power Pivot) in order to enable DAX.
TIP
To get a more in-depth understanding of DAX, see Rob Collie’s book DAX Formulas for PowerPivot: The Excel Pro’s Guide to Mastering DAX.
You edit a DAX formula by right-clicking any cell in the Table and selecting Table | Edit DAX, as shown in the next figure.
Table right-click submenu on an OLAP data source.
In the Edit DAX dialog box that appears, the Command Type combo box defaults to "Table". Change this to "DAX" to allow editing of the DAX expression in the text box, as shown in the next figure. Then you can edit and evaluate the DAX statement.
Edit DAX dialog box for editing DAX Table queries.
In this example, your goal is to retrieve all the unique values in the "LastName" field. The DAX query for this example is:
Evaluate(Summarize(Person, Person[LastName]))
While the open and close parentheses for the Evaluate statement aren't necessarily needed, they can make the statement easier to read and maintain.
You can use many different forms of DA
X queries to return results into an Excel Table. Power Pivot does not have to be installed to utilize this feature; you just need an OLAP data source.
While this method is very fast, it isn't always user friendly. Error messages, as shown in the next figure, don't give much information and can be cryptic. Currently, this is the most information you get if a query statement is invalid. In the specific case shown here, there is a "z" after the "LastName" field name. The only way you know if a DAX query is valid is to get no error message and have the query perform as expected in the Table.
DAX Table query error message.
Power Query
As part of the "Power" suite of tools, Power Query provides the ability to inspect, query, transform, organize, and expose data from many sources. Unlike traditional query editors, it utilizes the M language, which runs quickly and returns only the final query results.
TIP
Power Query is a free download from the Microsoft website. It is available for both 32- and 64-bit versions of Office 2010 and 2013. Search for “download power query” to find the current download location.
Once you have Power Query installed in either Excel 2010 or 2013, the POWER QUERY tab appears on the ribbon, as shown in the next figure.
POWER QUERY ribbon tab.
Power Query can pull data from many different kinds of data sources, including Tables. When you use a Table as the data source for a query, you see a new QUERY ribbon tab in the TABLE TOOLS contextual ribbon tab group in addition to the DESIGN tab, as shown in the next figure.
QUERY ribbon tab in the TABLE TOOLS contextual ribbon tab group when a Table returned from a Power Query is selected.
CAUTION
Once you use a Table as the data source for a Power Query, you can’t convert it to a normal range unless the query is removed from the workbook.
Power Query is exceptionally good at transforming data from forms that are difficult to manage and analyze into forms that are better structured. The following example takes a badly formed report and transforms it into a form more conducive to analysis.
The sample data is in a pivoted format (see the next figure), which means there is relational data extending down rows and to the right over columns. A better structure would be to have both the age range and the year extend down rows. Power Query can accomplish this transformation in a couple steps.
Starting sample data to be transformed into a usable data structure.
To start working with this data in Power Query, select POWER QUERY | Excel Data | From Table. If the source data is not already a Table, Power Query converts it to a Table.
Making a Table from Power Query.
NOTE
Creating a query from Power Query generates a data connection within the workbook to the final query output. The connection type is an OLEDB query, and the provider is listed as “Microsoft.Mashup.OleDb.1”.
After Power Query creates the query, it loads the data into the Power Query editor and displays it, as shown in the next figure.
Power Query Editor window.
To transform the data (that is, unpivot it), you need to select all the age columns in the Power Query editor. You can't select multiple columns in this editor, so select the leftmost column to unpivot—in this case the "Ages <18" column. Then, while holding down the SHIFT key, select the rightmost column to unpivot—in this case the "Age >56" column. In the Query Editor window, select Home | Transform | Unpivot. The data now appears in three columns, as shown in the next figure.
Unpivoted data.
Next, you need to rename the age column from "Attribute"—the default name that Power Query assigned—to "Age Range" by right-clicking the header of that column and selecting Rename.
Column right-click context menu in Power Query.
Renaming a column in Power Query.
You select HOME | Query | Apply & Close to complete the query. Power Query places the resulting Table in a new worksheet, as shown in the next figure, and closes the editor.
Final output from the Power Query example, unpivoted and in a Table.
CAUTION
Any changes you make to the Table that contains the query results will be overwritten when the query is refreshed.
NOTE
When creating a query in the Query Editor, the Load to worksheet check box is checked (which it is by default), Power Query creates a Table in a new worksheet or updates the existing Table, if already loaded. When the Load to Data Model check box is checked (which is not the default), Power Query loads the data into the Data Model for use with Power Pivot. If the query is edited and either check box is changed from checked to not checked, Power Query removes that destination.
In summary, this Power Query example involves three distinct actions to restructure the data into a usable format:
1. Define a Table as the data source for the query.
2. Unpivot the data columns.
3. Rename the "Attribute" column.
The following M code results from performing these three steps:
let
Source = Excel.CurrentWorkbook(){[Name="MakeTable"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source,{"Year"},"Attribute","Value"),
RenamedColumns = Table.RenameColumns(Unpivot,{{"Attribute", "Age Range"}})
in
RenamedColumns
Since this is a Power Query–generated Table, the contextual QUERY tab is displayed in the TABLE TOOLS ribbon tab group when the Table is selected. You use this ribbon tab to edit the query.
You can also use the Workbook Queries task pane to look at or edit a query. To display or hide this task pane, select POWER QUERY | Manage Queries | Workbook. The Workbook Queries task pane shows all queries in the workbook; from this task pane, you can edit or refresh any of them.
Workbook Queries task pane, visible when a Table is selected that is created from Power Query.
When you hover over a query in the task pane, as shown in the next figure, you get a quick analysis pop-up that displays the data (limited to the first 3,000 records), the columns (with a clickable hyperlink to select the sample data above), the last refresh time, and the load settings.
Quick analysis pop-up for the Workbook Queries task pane.
At the bottom of this quick analysis pop-up is an ellipsis button that you can click to see four additional commands for working with your query, as shown in the next figure.
Ellipsis submenu in the quick analysis pop-up.
When Power Query loads the query results to a new worksheet, it adds four additional custom Table styles to the workbook (see the next figure):
TableStyleQueryError
TableStyleQueryInfo
TableStyleQueryPreview
TableStyleQueryResult
Custom Table styles added with the Power Query.
TIP
If these Table styles were previously created in the workbook, Power Query does not overwrite them. The styles remain unchanged.
When you open a workbook that contains a Power Query–generated Table in Excel 2010 or 2013 without the Power Query add-in installed, you get the error message shown in the next figure.
Error when refreshing a Power Query–generated Table without Power Query installed.
When you open a workbook that contains a Power Query–generated Table in Excel 2007 and attempt a refresh, Excel displays a slightly different error message than before, as shown in the next figure.
Error when refreshing a Power Query table in Excel 2007.
6 Sorting,
Filtering, and Using Slicers
Two very common tasks when working with data are sorting and filtering. This chapter details how they apply to Tables. Generally speaking, it's not a good idea to have multiple Tables stacked horizontally when filtering. This is because a filtered row hides the entire sheet row, not just the rows of the Table being filtered. If Tables are to be stacked, it's generally recommended to stack them vertically; however, if you do this, their dimensions should be identical, or potential errors may arise when inserting Table rows. This chapter describes sorting and filtering pertaining to Tables; for more information on best practices related to these tasks, see Chapter 3.
Sorting
Tables provide sorting tools via the AutoFilter controls. When a Table is sorted, Excel automatically constrains the effects of the sort to the Table's data body range.
Sorting is an integral part of data analysis. Whether you're sorting text or numbers or sorting by color, Excel offers a wide variety of sorting methods to view Table data in many ways. In terms of the sort options available, there is no difference between sorting a standard range of cells and sorting a Table. There are three basic types of sorts:
Ascending—Numeric values sort low to high, and text values sort A to Z.
Descending—Numeric values sort high to low, and text values sort Z to A.
By color—You can sort a specific fill color to the top or sort using a custom order of cell fill colors. NOTE