Learn how to use the set of Power BI tools for powerful data analysis and data modelling in Microsoft Excel. With PowerPivot, you can mash up large volumes of data from various sources, perform information analysis rapidly and share insights easily. Use Power View to bring your data to life with visuals such as charts, graphs, maps and tables.
PREREQUISITES
Advanced Microsoft Office Excel
TARGET AUDIENCE
Data Analyst, Accountants, Excel Professionals, Business Intelligence Experts
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
• Pivoting and Unpivoting 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
MODULE 7: CONNECTING MULTIPLE TABLES
• Filter Direction
• Hiding from Client Tools
• Defining Hierarchies
MODULE 8: 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 9: DAX SYNTAX AND OPERATORS
• Common DAX Functions and Categories
• Basic Math and Stats Functions
• COUNT Functions
• LOGICAL Functions IF,AND,OR
• SWITCH and SWITCH True
• Text Functions
MODULE 10: ADVANCED DATA ANALYSIS EXPRESSIONS
• 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
• Iterator X RANKX Functions
• Basic Date and Time Functions
• Time Intelligence Functions
• Speed and Performance Configuration
MODULE 11: DATA VISUALIZATION USING PIVOT CHART, POWER MAP AND POWER VIEW
• Exploring Pivot Chart
• Exploring Power Map
• Exploring Power View
MODULE 12: DIFFERENT VISUALS IN POWER VIEW
• DEMO: Matrix Visuals
• DEMO: Date Slicers
• DEMO: Cards and KPI’s
• DEMO: Text Cards
• DEMO: Map Visuals
• DEMO: Tree Maps
• DEMO: Line and Area Charts
• DEMO: Trend-lines and Forecast
• DEMO: Gauge Charts