Microsoft Excel: Business Intelligence w/ Power Query & DAX

Microsoft Excel: Business Intelligence w/ Power Query & DAX

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 87 lectures (7h 14m) | 2.16 GB

Learn advanced Excel for data analysis & business intelligence (Power Query, Power Pivot & DAX language. Excel 2013+)

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, API services 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.

If you’re ready to take your Excel game to new heights and join the leading edge of analytics & business intelligence, this course is for you. It’s time to stop fighting with tedious, manual tasks and struggling with “old-school” Excel; join me on this journey and emerge a certified excel analytics NINJA.

What you’ll learn

  • Get up & running with Excel’s game changing data modeling & business intelligence tools
  • Learn how to use Power Query, Power Pivot & DAX to absolutely revolutionize your workflow in Excel
  • Master unique tips, tools and case studies that you won’t find in ANY other course, guaranteed
  • Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
  • Build pro-quality business intelligence solutions to blend and analyze data from multiple sources
Table of Contents

Getting Started
1 Course Structure & Outline
2 IMPORTANT Versions & Compatibility
3 READ ME Important Notes for New Students
4 Introducing the Course Project
5 DOWNLOAD Course Resources
6 Setting Expectations

Intro to “Power Excel
7 Understanding the “Power Excel” Workflow
8 Power Query + Power Pivot Best Thing to Happen to Excel in 20 Years
9 When to use Power Query & Power Pivot
10 HOMEWORK Intro to Power Excel

Connecting & Transforming Data with Power Query in Excel
11 Introduction
12 Getting to Know Power Query in Excel
13 Exploring Excel’s Power Query Editor
14 Power Query Data Loading Options
15 IMPORTANT Updating Locale Settings
16 Applying Basic Table Transformations with Power Query
17 Power Query Demo Text Tools
18 Power Query Demo Number & Value Tools
19 Power Query Demo Date & Time Tools
20 PRO TIP Creating a Rolling Calendar with Power Query
21 Power Query Demo Generating Index & Conditional Columns
22 Power Query Demo Grouping & Aggregating Records
23 Power Query Demo Pivoting & Unpivoting Data
24 Modifying Excel Workbook Queries
25 Merging Queries with Power Query
26 Appending Queries with Power Query
27 Power Query Demo Connecting to a Folder of Files
28 Excel Power Query Best Practices
29 HOMEWORK Connecting & Transforming Data with Power Query in Excel

Building Table Relationships with Excel’s Data Model
30 Introduction
31 Meet Excel’s Data Model
32 The Data Model Data vs. Diagram View
33 Principles of Database Normalization
34 Understanding Data Tables vs. Lookup Tables
35 Benefits of Relationships vs. Merged Tables
36 Creating Table Relationships in Excel’s Data Model
37 Modifying Data Model Table Relationships
38 Managing Active vs. Inactive Table Relationships
39 Understanding Relationship Cardinality
40 Connecting Multiple Data Tables in the Data Model
41 Understanding Filter Flow
42 Hiding Fields from Excel Client Tools
43 Defining Hierarchies in a Data Model
44 Excel Data Model Best Practices
45 HOMEWORK Building Table Relationships with Excel’s Data Model

Analyzing Data with Power Pivot & DAX
46 Introduction
47 Creating a Power Pivot Table
48 Power Pivots vs. “Normal” Pivots in Excel
49 Introducing Data Analysis Expressions (DAX)
50 Understanding DAX Calculated Columns
51 Understanding DAX Measures
52 Creating Implicit DAX Measures
53 Creating Explicit DAX Measures with AutoSum
54 Creating Explicit DAX Measures with Power Pivot
55 Understanding DAX Filter Context
56 Step-by-Step DAX Measure Calculation
57 RECAP Calculated Columns vs. DAX Measures
58 Excel Power Pivot & DAX Best Practices
59 HOMEWORK Analyzing Data with Power Pivot & DAX

Common DAX Formulas & Functions
60 Introduction
61 Understanding DAX Formula Syntax & Operators
62 Common DAX Function Categories
63 DAX Demo Basic Math & Stats Functions
65 HOMEWORK Math & Stats Functions
66 DAX Demo Logical Functions (IFANDOR)
68 DAX Demo Common Text Functions
69 HOMEWORK Logical & Text Functions
71 DAX Demo Adding Filter Context with FILTER (Part 1)
72 DAX Demo Adding Filter Context with FILTER (Part 2)
73 DAX Demo Removing Filter Context with ALL
75 DAX Demo Joining Data with RELATED
76 DAX Demo Iterating with SUMX
77 DAX Demo Iterating with RANKX
78 HOMEWORK Iterator (X) Functions
79 DAX Demo Basic Date & Time Functions
80 DAX Demo Time Intelligence Formulas
81 HOMEWORK Time Intelligence
82 DAX Speed & Performance Considerations
83 DAX Best Practices

Wrapping Up
84 Introduction
85 Data Visualization Options in Excel
86 Sneak Peek Microsoft Power BI
87 BONUS LECTURE More from Maven