Course Outline

MODULE 1: INTRODUCTION TO EXCEL POWER TOOLS

• Setting up Excel Power Tools in COM Add-Ins
• The Power Excel Workflow
• When to Use Power Query and Power Pivot in Excel

MODULE 2: THE POWER QUERY EDITOR

• Meet Power Query Aka Get and Transform
• The Query Editor Interface
• Options for Loading Data in Excel
• Basic Power Query Table Transformations

MODULE 3: DATA SHAPING WITH POWER QUERY EDITOR

• Text-Specific Tools
• Number-Specific Tools
• Date-Specific Tools
• Creating a Rolling Calendar
• Index and Conditional Columns in Power Query
• Grouping and Aggregating Data in Power Query

MODULE 4: ADVANCED POWER QUERY EDITOR

• Merging Queries
• Appending Queries
• Connect Excel to Folder of Files

MODULE 5: MEET EXCEL’S DATA MODEL

• Data vs Diagram Views
• Database Normalization
• Data Tables vs Lookup Tables
• Relationships vs Merge Tables

MODULE 6: CREATING TABLE RELATIONSHIPS

• Modifying Table Relationships
• Active vs Inactive Relationships
• Relationship Cardinality
• Filter Direction
• Hiding from Client Tools
• Defining Hierarchies

MODULE 7: POWERPIVOT AND DATA ANALYSIS EXPRESSIONS

• Creating PowerPivot Table
• Power Pivot vs Normal Pivot
• Introduction to Data Analysis Expressions
• Calculated Columns
• DAX Measures
• Creating Implicit Measures
• Creating Explicit Measures (AutoSum)
• Creating Explicit Measures (PowerPivot)
• Understanding Filter Context
• Step-by-Step Calculation
• Power Pivot Best Practices

MODULE 8: DAX SYNTAX AND OPERATORS

• Common DAX Functions and Categories
• Basic Math and Stats Functions
• COUNT Functions
• LOGICAL Functions IF, AND, OR
• Text Functions

MODULE 9: ADVANCED DATA ANALYSIS EXPRESSION

• Adding Filter Context Part 1
• The Calculate Function
• Adding Filter Context Part 2
• Removing Filter Context With ALL
• Joining Data with RELATED
• Iterator X SUMX Functions
• Basic Date and Time Functions
• Time Intelligence Functions
• Speed and Performance Configuration

MODULE 10: DATA VISUALIZATION USING PIVOT CHART

• Data Visualization Principles
• Setting up a Pivot Chart
• Create pivot chart from Power Pivot Sources
• Adding a Bar Chart for Data comparisons
• Adding a Pie Chart to Display data Proportions
• Adding a Line Chart to display trends overtime
• Adding a Slicer to filter data using Text controls
• Adding a Timeline to filter data using Date Controls

TARGET AUDIENCE

Data Analyst, Accountants, Excel Professionals, Business Intelligence Experts

PREREQUISITES

Advanced Microsoft Excel