Excel Power Pivot, DAX & Business Intelligence
What you’ll learn
- Get up & running with Excel’s game changing data modeling & business intelligence tools
- Master unique tips, tools and case studies that you won’t find in ANY other course, guaranteed
- Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
- Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
Requirements
- IMPORTANT: You need a version of Excel that is compatible with Power Pivot (Excel 2013/2016/2019 Standalone, Office 365, Enterprise E3/E5, Office Professional 2016, etc.)
- This course is designed for PC users (Power Pivot is currently unavailable with Excel for Mac)
- Experience with Excel PivotTables and formulas & functions is strongly recommended
COURSE DESCRIPTION:
This course introduces Microsoft Excel’s powerful data modeling and business intelligence tools: Power Query, Power Pivot, and Data Analysis Expressions (DAX). If you’re looking to become a power Excel user and absolutely supercharge your Excel analytics game, this course is the A-Z guide that you’re looking for.
I’ll introduce the “Power Excel” landscape, and explore what these Excel tools are all about and why they are changing the world of self-service business intelligence. Together, we’ll walk through the Excel BI workflow, and build an entire Excel data model from scratch:
- First we’ll get hands-on with Power Query; a tool to extract, transform, and load data into Excel from flat files, folders, databases, and more. We’ll practice shaping, blending and exploring our project files in Excel’s query editor, and create completely automated loading procedures inside of Excel with only a few clicks.
- From there we’ll dive into Data Modeling 101, and cover the fundamentals of database design and normalization (including table relationships, cardinality, hierarchies and more). We’ll take a tour through the Excel data model interface, introduce some best practices and pro tips, and then create our own relational database to analyze throughout the course.
- Finally, we’ll use Power Pivot and DAX to explore and analyze our Excel data model. Unlike traditional Excel Pivot Tables, Power Pivot allows you to analyze hundreds of millions of rows across multiple data tables (inside of Excel!), and create supercharged calculated fields using a formula language called Data Analysis Expressions (or “DAX” for short). We’ll cover basic DAX syntax, then introduce some of the most powerful and commonly-used functions — CALCULATE, FILTER, SUMX and more.
Course content
- Intro to “Power Excel”
- Understanding the “Power Excel” Workflow
- Power Query & Power Pivot: Best things to happen to Excel in 20 years
- When to use Power Query & Power Pivot
- Connecting & Transforming Data with Power Query in Excel
- Getting to Know Power Query in Excel
- Exploring Excel’s Power Query Editor
- Connecting & Transforming Data with Power Query in Excel
- Power Query Data Loading Options
- Applying Basic Table Transformations with Power Query
- Power Query with Text Tools, Number & Value & Data & Time
- Creating a Rolling Calendar with Power Query
- Generating Index & Conditional Columns
- Grouping & Aggregating Records
- Pivoting & Unpivoting Data
- Modifying Excel Workbook Queries
- Merging Queries with Power Query
- Appending Queries with Power Query
- Connecting to a Folder of Files
- Building Table Relationships with Excel’s Data Model
- Introduction
- Meet Excel’s “Data Model”
- The Data Model Data vs. Diagram View
- Principles of Database Normalization
- Understanding Data Tables vs. Lookup Tables
- Benefits of Relationships vs. Merged Tables
- Modifying Data Model Table Relationships
- Managing Active vs. Inactive Table Relationships
- Understanding Relationship Carnality
- Connecting Multiple Data Tables in the Data Model
- Hiding Fields from Excel Client Tools
- Defining Hierarchies in a Data Model
- Excel Data Model Best Practices
- Analyzing Data with Power Pivot & DAX
- Introduction
- Creating a “Power” Pivot Table
- Power Pivots vs. “Normal” Pivots in Excel
- Introducing Data Analysis Expressions (DAX)
- Understanding DAX Calculated Columns
- Understanding DAX Measures
- Creating Implicit DAX Measures
- Creating Explicit DAX Measures with AutoSum
- Creating Explicit DAX Measures with Power Pivot
- Understanding DAX Filter Context
- Analyzing Data with Power Pivot & DAX
- Common DAX formulas & Functions
- Introduction
- Common DAX Formulas & Functions
- Basic Math & Stats Functions
- COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS
- Logical Functions (IF/AND/OR)
- SWITCH & SWITCH(TRUE)
- Common Text Functions
- Logical & Text Functions
- CALCULATE
- Adding Filter Context with FILTER
- Removing Filter Context with ALL
- Joining Data with RELATED
- Iterating with SUMX, RANKX
- Date & Time Functions
- Time Intelligence Formulas
Trainer : Mr. Irfan Bakaly (MVP)
- Microsoft Most Valuable Professional (MVP)
- Microsoft Certified Professional
- Microsoft certified Analyzing and Visualizing Data with Power BI
- Microsoft Certified Office Specialist of Excel 2013/2016
- Microsoft Certified in Managing Projects with Microsoft Project
- 20+ years of hands-on experience