This lesson is excerpted from the Expert Skills book
This lesson is excerpted form the book: Learn Excel 365 Expert Skills with The Smart Method: Fourth Edition: updated for the Jul 2020 Semi-Annual version 2002.
This is the one of the first lessons in the session: Power Pivot, Data Modeling, OLAP and Business Intelligence.
- Download the sample file Stock List-2 by clicking the button above.
- Open the sample file you’ve just downloaded using Excel.
- Open the Power Pivot window.
You can do this by clicking Power Pivot–>Data Model–>Manage.
The Power Pivot window opens:
This is your first view of the Power Pivot application.
The differences between the Power Pivot window and Excel
The Power Pivot window seems very similar to Excel but you are really looking at a completely different application that has been engineered to “look and feel” like Excel. This is intended to make you feel instantly at home with Power Pivot.
You can see that there is a data grid that is similar to Excel with rows and columns. You can also see tabs showing that there are two tables in the above data model (very similar to two worksheets in a workbook).
Just like Excel you can:
- Rename and delete columns.
- Change column widths.
- Insert new columns.
- Click and drag columns to new locations.
- Rename and delete sheet tabs.
- Click and drag sheet tabs to re-arrange them.
- Use AutoSum to aggregate all of the values in a column. This creates a calculated measure.
The Expert Skills book has a lesson (Create a DAX calculated measure) later in the book that teaches how to create a calculated measure.
- Format cells.
- Sort columns.
Unlike Excel you can't:
- Type a value into a cell or change any of the information you see displayed (although you can refresh the data from the original data source).
- Have more than one table in a sheet. A Power Pivot sheet contains a single table rather than a worksheet.
- Refer to a cell using A1 notation (A1 notation means cell references such as B12 or A1:C14). Power Pivot does not recognize letters to identify columns. Columns must be referred to by name.
- Assign different data types within a column. You cannot, for example, have text in some cells and numbers in other cells in the same column. An earlier lesson in the Expert Skills book (Understand data types) explains what data types are and which data types are supported by Power Pivot).
- Have more than one formula in a column. When a formula is added to a Power Pivot column it always applies to every cell in that column.
Some things Power Pivot can do that Excel cannot
- Create relationships between tables.
- Assign a larger range of data types to columns.
- Use DAX functions (the Expert Skills book covers DAX functions in Session Thirteen: An introduction to DAX).
- Create calculated measures. The Expert Skills book covers teaches how to create measures in: Lesson 13 3: Create a DAX calculated measure.
- Work with Big Data. An Excel worksheet can contain a maximum of just over a million rows. A Power Pivot table can contain a maximum of just over a thousand million rows.
- Produce extremely fast (often perceived as instant) results even when analyzing data sets containing many millions of rows. Power Pivot uses Microsoft’s xVelocity in-memory analysis engine. This engine can scan billions of data rows per second and can produce reports in a tiny fraction of the time needed by Excel.
- Close the Power Pivot window.
- Close Excel without saving the workbook.
Additional information (shown in the sidebars of the Expert Skills book)
You can paste data directly into a new data model table
To do this:
- Select and copy the range of data that you want to import into a new Power Pivot table.
- Open the Power Pivot window.
- Click: Home–>Clipboard–>Paste (from within the Power Pivot window).
- Provide a name for the new table when prompted.
If data in the workbook changes the data model will not automatically update
The data you are looking at is an independent copy of the data residing in the Excel Product and Category tables.
If the data in the Product and Category tables changes, you will not see any change in the data model (unless you refresh the model from the original source data).
Do not import or transform data using Power Pivot
The Power Pivot application is a tool to construct and refine data models.
Power Pivot is not a good tool for importing data into the data model.
You should always import data into the data model using Get & Transform. In the Expert Skills book the sample file you downloaded was prepared using best-practice by the student in the Get & Transform session.
Using Get & Transform provides powerful data transformation features that enable you to edit and refine your data in the future.
The best-practice rule:
Do not use Power Pivot to import or transform data.
Is included in Appendix A of the Expert Skills book along with 24 other best-practice rules to follow when working with Power Pivot. Get & Transform and DAX.