What is Power Pivot?
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
Power Pivot is one of three data analysis tools available in Excel:
Course overview?
Power Pivot for Excel enables you to transform enormous quantities of data with incredible speed into meaningful information to get the answers you need in seconds, all with the comfort of familiar Excel tools and features.
Import millions of rows of data from virtually any source including relational databases, data feeds, Reporting Services, text files, multidimensional sources, cloud services, data feeds, Excel files, and data from the Web into a single Excel workbook, Create relationships between the different sources just like Access. Build interactive reports with PivotTables, PivotCharts and Slicers, and then further analyze the data so that you can make timely business decisions. Power Pivot calculations and analysis are extremely fast. You can process millions of rows in about the same time as thousands.
Data Analysis Expressions (DAX) is a new formula language that extends the data manipulation capabilities of Excel to enable more sophisticated and complex grouping, calculation, and analysis like never before. The DAX formula language is very similar to that of Excel formulas.
Learning outcomes
Upon completing this course, you will be able to:
- Create a Power Pivot table by drawing data from several different tables.
- Develop simple calculated metrics with consistent formatting.
- Assemble multiple pivot tables, charts, and dimensions into a dashboard that provide us with different angles of analysis.
- Apply DAX formula language to create measures and columns.
- Manipulate context to calculate more advanced metrics.
- Utilize DAX Advanced Time Intelligence to create period comparisons and KPIs.
Target group
This Power Pivot course is perfect for professionals who have a solid understanding of excel and want to expand Excel’s business intelligence capabilities. This course is designed to equip anyone who desires to begin a career in business analysis—or other roles that require displaying and analyzing data sets—with a fundamental knowledge of this core tool
Course contents
- What is Power Pivot
- Sneak Peek of how it works
- Benefits and Limitations
- Versions and Installation
- Compatibility between
- Tour of Power Pivot Window
- Preparing Your Data
- Excel Tables
- Importing Data from Access
- Importing Data from Linked Excel Tables
- Importing Data from external Excel Files
- Importing a Text File
- Copying and Pasting Data
- Importing from Other Sources
- Date/Calendar Table
- Creating a Date Table
- Data Formats
- Sheets and Columns
- Editing and Adding Data
- Table Properties
- Filtering and Sorting
- Referencing Columns
- Using Functions
- Referencing another Calculated Column
- RELATED Function
- Aggregation Functions
- Formula Errors
- Calculated Columns Recap & Rules
- Introduction and Rules
- Defining Relationships
- AutoDetect Relationships
- Editing Relationships
- Multiple Relationships
- Working in Diagram View
- Ideal Data Structure
- Optimising Data Structures
- Inserting a Power Pivot PivotTable
- Filters and Slicers
- Sorting Options
- Sorting and Grouping Dates
- Show Values As
- Formatting PivotTables
- Chart & Table Combos
- Flattened PivotTable
- Arranging and Formatting Slicers
- GETPIVOTDATA Function
- Implicit Measures
- Explicit Measures
- Context in DAX Formulas
- DAX Functions and Operators
- Overriding Context Rules
- Referencing Measures in Other Measures
- Measure Portability
- Understanding and Validating Measures
- Finding, Editing and Deleting Measures
- Interpreting Errors
- Expressions
- CALCULATE
- COUNTROWS
- FILTER
- DISTINCTCOUNT
- ALL
- ALLEXCEPT
- ALLSELECTED
- X Functions
- Time Intelligence Functions
- PREVIOUSYEAR & NEXTYEAR
- DATESYTD & TOTALYTD
- DATESBETWEEN
- FIRSTDATE & LASTDATE
- CLOSING/OPENINGBALANCE
- DATEADD
- PARALLELPERIOD
- % Year on Year & Error Handling
- Named Sets
- Perspectives
- Hierarchies
- Named Sets vs Perspectives vs Hierarchies
- Creating KPI’s
- Introduction to CUBE Functions
- Writing CUBE Formulas
- CUBESET & CUBERANKEDMEMBERl
