Lesson 1.1: Getting Familiar with Power Query

• Introduction to Power Query
• Meet Power Query aka Get Transform
• Data Sources Supported by Power Query
• Understanding the Navigator Pane
• Query Editor Tabs

Lesson 1.2: Common Data Extraction using Power Query

• Data Loading Options
• Creating a New Query from a Text File
• Creating a New Query from a CVS File
• Creating a New Query from an Excel workbook
• Creating a New Query from an Excel Table/Range

Lesson 1.3: Advance Data Extraction using Power Query

• Getting data from XML files
• Creating a New Query from the Web
• Creating a New Query from Access Database
• Connect a New Query to a folder of files
• Combining Data from Multiple Text Files

Lesson 2.1: Understanding Data Types

• Defining data types and errors
• Setting the Data Type of a Column
• Changing Data Types and Locales

Lesson 2.2: Transformation Tasks with Column

• Naming Columns
• Moving Columns
• Removing Columns
• Splitting Columns
• Merging Columns

Lesson 2.3: Filtering and Sorting Rows

• Filtering Rows Using Auto-Filter
• Filtering Rows Using Number, Text, and Date Filters
• Filtering Rows by Range
• Removing Duplicate Values/Records
• Filtering Out Rows with Errors
• Sorting a Query

Lesson 3.1: Changing Values in a Query

• Replacing Values with Other Values
• Filling in blank fields
• Concatenating columns
• Changing case
• Trimming and cleaning text
• Extracting the left, right, and middle values
• Number-specific query editing tools
• Date-specific query editing tools

Lesson 3.2: Creating Custom Columns

• Add index and conditional columns
• Inserting Calculated Columns
• Custom Columns with M Calculations
• Inserting Custom Date and Time Columns
• Add a column from an example

Lesson 3.3: Extending Power Query with Custom Functions

• Creating and Using a Basic Custom Function
• Preparing for a parameter query
• Creating the base query
• Creating the parameter query

Lesson 3.4: Advance Power Query Features

• Filling Up and Down to Replace Missing Values
• Grouping and Aggregating Data
• Pivoting and Unpivoting Data with Power Query
• Transposing a Table

Lesson 4.1: Making Queries Work Together

• Reusing Query Steps
• Renaming Query Steps
• Understanding the Append Feature
• Creating the needed base queries
• Appending the data
• Understanding the Merge Feature
• Understanding Power Query joins
• Merging queries

Lesson 4.2: Data Destinations

• Choosing a Destination for Your Data
• Loading Data to the Worksheet Using the Default Excel Table Output
• Loading Data to Your Own Excel Tables
• Loading Data to Data Model/Power Pivot
• Loading Data Connection Only

Lesson 4.3: Loading Data to the Excel Data Model

• Viewing Tables in the Excel Data Model
• Advantages of Using the Excel Data Model
• Power Query and Table Relationships
• Breaking Changes

Lesson 4.4: Refreshing Queries

• Refreshing Queries Manually
• Automating Data Refresh
• Power Query Best Practices

Lesson 5.1: Getting Familiar with Power Pivot

• Introduction to Power Pivot
• Understanding Power Pivot
• Limitations of the Internal Data Model
• Understanding Acceptable Data Types
• Launching Power Pivot
• Tour of Power Pivot Window
• Power Pivot best practices

Lesson 5.2: Common Data Extraction using Power Pivot

• Preparing Your Data
• Adding Excel Tables to Power Pivot
• Importing a Text File
• Copying and Pasting Data

Lesson 5.3: Advance Data Extraction using Power Pivot

• Importing Access Tables
• Importing Data from external Excel Files
• Adding and Maintaining Data in Power Pivot

Lesson 6.1: Introduction to Data Model

• Example of Data Model
• Meet the Excel data model
• Access the Data Model
• The Data Model Window
• Understanding Key Fields
• Data vs. Diagram View
• Database normalization

Lesson 6.2: Data Model Vs. Excel Table

• Data Models Table Types
• Data tables versus lookup tables
• Relationships versus merged tables

Lesson 6.3: Working with Relationships

• Primary & Foreign Keys
• Create Table Relationships
• Connecting Lookups To Lookups
• Modify table relationships
• Active versus inactive relationships
• Relationship cardinality
• Connect multiple data tables

Lesson 6.4: Understanding Data Model Relationship

• Filter direction
• Hide fields from client tools
• Define hierarchies
• Data model best practices

Lesson 7.1: Creating and Applying a Date Table

• Creating a Date Table in Excel
• Marking a Table as a Date Table
• Creating the Date Table in Power Pivot
• Adding Sort by Columns to the Date Table
• Adding the Date Table to the Data Model

Lesson 8.1: Introduction to Calculation

• Types of Calculations in Power Pivot
• Creating a Calculated Column
• Creating a Calculated Field (Measures)
• Creating Implicit Measures
• Creating Explicit Measures (Power Pivot)
• Creating a Key Performance Indicator (KPI)

Lesson 8.2: Using Data Analysis Expressions

• Understanding Data Analysis Expressions (DAX) Formulas
• Where to use DAX formulas
• Understanding the DAX syntax
• COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS
• Logical Functions (IF, AND, and OR)
• The CALCULATE function
• The FILTER Function
• Join data with RELATED
• Iterator functions: SUMX
• Iterator functions: RANKX
• Speed & Performance Considerations
• Dax Best Practices

Lesson 8.3: Time Intelligence Formulas

• Introductions
• PREVIOUSYEAR & NEXTYEAR
• DATESYTD & TOTALYTD
• DATESBETWEEN
• FIRSTDATE & LASTDATE
• CLOSING/OPENINGBALANCE

• Creating Pivot Tables
• Filtering Data using Slicers
• Add Visualizations to a Pivot Table
• Creating Pivot Charts
• Formatting Pivot Charts
• Using Multiple Charts and Tables