For data analysts, business or policy analysts, financial analysts, managers, business intelligence professionals, or anyone who needs to improve their understanding of, or ability to work with data derived from multiple sources and/or large volumes of data using Power Pivot for Excel. Some basic experience with Excel and Pivot Tables is desirable.

Business intelligence (BI) involves a set of theories, methodologies, architectures, and technologies to transform raw data into meaningful and useful information for business analysis purposes. To this end, Microsoft Excel’s Power Pivot offers an agile and cost effective approach to BI. In this course, you will learn how to use Excel and its Power Pivot tools to mine large amounts of sophisticated data to provide better data analysis, business insights and support informed decisions. Additionally, you will learn how to employ PivotTables, PivotCharts and VBA (Visual Basic for Applications) to present critical information to senior management through dashboards, enabling them to see a complete picture for a given initiative, project, or their organization.

Chapter 1: Thinking Like a Database

• Exploring the Limits of Excel and How Databases Help
• Understanding database terminology:
• Tables
• Records, fields, and values
• Queries
• Understanding relationships between tables
• Scalability and transparency of analytical processes
• Separation of data and presentation layers
• Benefits of structured data models
• How relational logic improves data analysis
• Common Excel pitfalls that databases solve

Chapter 2: Introducing Power Pivot

• Understanding the Power Pivot internal data model
• Activating the Power Pivot Add-In
• Preparing and linking Excel tables to Power Pivot
• Creating and managing relationships
• Using Power Pivot data in pivot tables
• Benefits of the internal data model
• Loading multiple tables into the data model
• Refreshing Power Pivot data
• Limitations of Power Pivot
• Best practices for Power Pivot setup

Chapter 3: The Pivotal Pivot Table 

• Introducing the Pivot Table and its four areas:
• Values area
• Row area
• Column area
• Filter area
• Creating and modifying a pivot table
• Adding report filters and slicers
• Customizing layout and formatting
• Changing summary calculations
• Sorting and hiding data item
• Using slicers and timeline slicers

Chapter 4: Using External Data with Power Pivot

• Loading data from computer files
• Loading data from servers
• Loading data from other places
• Refreshing and managing external connections
• Manual and automatic refresh options
• Managing connection settings
• Using data from flat files (Excel/text)
• Loading from Access and SQL Server
• Editing Power Pivot connections
• Avoiding Refresh All for performance

Chapter 5: Working Directly with the Internal Data Model

• Feeding data directly into the data model
• Adding and removing tables
• Creating pivot tables using the model
• Using multiple external data tables
• Managing table structure in the model
• Organizing related data sources
• Resolving conflicts in table structure
• Best practices for table integration
• When to use the internal model
• Limitations of direct manipulation

Chapter 6: Adding Formulas to Power Pivot

• Using calculated columns to enhance data
• Formatting and hiding calculated columns
• Creating calculated measures
• Editing and deleting measures
• Using DAX functions safely
• Referencing fields across tables
• Differences between measures and columns
• Using conditional logic in DAX
• Best practices for calculations
• Optimizing formula performance

Chapter 7: Introducing Power Query

• Installing and activating Power Query
• Understanding query steps
• Refreshing and managing Power Query data
• Managing existing queries
• Using column-level actions
• Using table-level actions
• Creating queries from various sources
• Best practices for managing queries
• Common pitfalls to avoid
• How Power Query complements Power Pivot

Chapter 8: Power Query Connection Types

• Importing data from files:
• Excel workbooks
• CSV, text, XML
• Folders of files
• Importing data from database systems:
• SQL Server
• Access
• Other systems
• Getting data from other systems
• Managing data source settings
• Understanding privacy levels
• Configuring connection authentication

Chapter 9: Transforming Your Way to Better Data

• Removing duplicates and filling blanks
• Changing text case and trimmin
• Finding and replacing values
• Splitting and extracting column values
• Concatenating columns and custom columns
• Understanding data type conversions
• Adding conditional logic to columns
• Pivoting and unpivoting columns
• Grouping and aggregating data
• Reordering, filtering, and renaming columns

Chapter 10: Making Queries Work Together

• Reusing query steps efficiently
• Understanding the append feature
• Creating base queries for append
• Using merge feature for joins
• Understanding Power Query joins
• Resolving conflicts in joined data
• Creating composite queries
• Cleaning merged results
• Naming and documenting queries
• Saving and reloading merged queries

Chapter 11: Extending Power Query with Custom Functions

• Creating and using basic custom functions
• Merging data from multiple Excel files
• Creating parameter queries:
• Base query preparation
• Parameter input
• Applying parameters to queries
• Using functions with conditional logic
• Nesting functions within queries
• Organizing function libraries
• Best practices for maintainability

Chapter 12: Ten Ways to Improve Power Pivot Performance

• Limit the number of rows and columns
• Use views instead of raw tables
• Avoid multi-level relationships
• Offload processing to the database server
• Avoid columns with non-distinct values
• Limit slicer use in reports
• Create slicers only on dimension fields
• Disable cross-filtering for some slicers
• Use calculated measures instead of columns
• Optimize relationships for speed

Chapter 13: Ten Tips for Working with Power Query

• Use the Workbook Queries Pane effectively
• Group queries for organization
• Rename steps for clarity
• Quickly create reference tables
• Copy queries to save time
• Set default load behavior
• Preventing automatic data type changes
• Disable privacy settings for speed
• Disable relationship detection
• Use keyboard shortcuts for efficiency