Introduction To PowerPivot for Excel 2013
Basics of PowerPivot: Why PowerPivot?, Add to Data Model, Relationships, Calculated Column, Calculated Field, Build report with PivotTable based on Data Model:
(00:22) Smartest People with PowerPivot
(01:31) Which Versions of Excel 2013 contain PowerPivot
(01:46) Install PowerPivot for Excel 2013
(02:14) What aspects of PowerPivot is so amazing
(06:46) When not to use PowerPivot
(07:28) Amazing PowerPivot Columnar Database, also known as Data Model or PowerPivot xVelocity engine
(09:46) Example of Multiple tables when you don’t want to use PowerPivot, but instead would want to use Helper Columns and VLOOKUP functions. In essences, PowerPivot will not permanently replace all VLOOKUPs. When you have multiple tables, sometimes PowerPivot will be the perfect solution, other times VLOOKUP will be the perfect solution.
(14:19) Start of PowerPivot Example
(14:48) Convert Proper Data Sets to Excel Tables BEFORE importing into PowerPivot, Create Table Keyboard = Ctrl + T, Name Table Keyboard = Alt, J, T, A
(15:43) Why Excel Import must be Excel Table.
(15:55) “Fact Tables and Dimension Tables” = Database terms, and, “Transaction Tables and Lookup Tables” = Excel Terms”
(16:22) Import Excel Tables into “Manage Data Model” window using “Add to Data Model”, Keyboard = Alt, B, Y
(16:44) Manage Data Model window
(17:56) We start to build the “Data Model” by bringing the Tables into the “Manage Data Model” window.
(18:11) Building Relationships between Tables using the “Diagram View”
(18:25) Arranging Tables in “Star Schema” view or “Criteria/Filters Flow Downward” view.
(19:05) Continuing to build the Data Model by adding Relationships between Tables. Relationships replace VLOOKUPs.
(20:26) One To Many Relationship
(22:16) PivotTable mistake if you don’t create correct Relationships
(22:25) Multiple Tables appear in PivotTable Field list.
(23:26) Correct PivotTable with correct Relationships: Units by Region PivotTable. This means we can drag and drop fields into the PivotTable from Multiple Tables.
(24:21) Format Tables in “Manage Data Model” window.
(25:20) Sort columns in Tables in “Manage Data Model” window.
(25:35) Adjust view of Measure Grid below Tables.
(25:44) Create Calculated Column in in “Manage Data Model” window.
(26:22) RELATED function replaces VLOOKUP
(26:39) We must use Formula Bar to create formulas.
(27:11) Table Names and Field Names are similar to Excel Table Formula Nomenclature (Structured References): Tables Names are followed by Field Names in square brackets.
(27:39) Row Context for Calculated Columns: No more Cell References like in normal Excel formulas.
(28:58) Formula for Net Revenue given price, discount and units.
(29:04) Formula Convention for Table Names and Field Names: Tables Names are followed by Field Names in square brackets. From Russo and Ferrari.
(30:36) Functions in PowerPivot are called DAX functions. DAX = Data Analysis Expressions.
(30:58) Number Formatting From Calculated Column Formula appears in PivotTable!!!!
(32:23) Implicit vs. Explicit Formulas. Explicit is better because you can use them in any other PivotTable or Calculated Column or Calculated Field (Measure)
(33:00) Create Calculated Field (Measure) in the Measure Grid below the Table. These are Explicit Formulas.
(33:56) Conventions for created Calculated Fields.
(34:52) Add Number Formatting to Calculated Field.
(36:00) Filter Context: Calculated Fields (Measures) resect the Criteria dropped into the Row/Column/Filter and Slicer area of PivotTables. The Criteria in the PivotTable actually filters the underlying Data Model.