Unit 1 - Power Query Fundamentals

•     Default Settings

•     Extracting Data: The 4 steps

•     Transforming Data

Unit 2 - Query Management

•     Using a Multi-Query Architecture

•     Referencing Queries

•     Choosing Query Load Destinations

•     Keeping Queries Organized

•     Splitting an Existing Query

Unit 3 - Data Types and Errors

• Data Type vs Formats

• Common Error Types

• Query Error Auditing

Unit 4 - Moving Queries Between Excel & Power BI

• Copying Queries Between Solutions

• Importing Excel Queries to Power BI

Unit 5 - Importing from Flat Files

• Understanding How Systems Import Data

• Importing Delimited Files

• Importing Non-Delimited Text Files

• Basic Cleaning and Transformation

Unit 6 - Importing Data from Excel

• Data Within the Active Workbook

• Data From Other Workbooks

Unit 7 - Simple Transformation Techniques

• Un-Pivoting the Curse of Pivoted Data

• Pivoting Data

• Splitting Columns

• Filtering and Sorting

• Grouping Data

Unit 8 - Appending Data

• Basic Append Operations

• Combining Queries with Differing Headers

• Appending Tables & Ranges in the Current File

Unit 9 - Combining Files – A Case Study

• Sample Case Background

• Process Overview

• Step – by step Implementation

Unit 10 - Merging Data

• Merging Basics

• Join Types

• Cartesian Products (Cross Joins)

• Approximate Match Joins

• Fuzzy Matching

Unit 11 - Web Based Data Sources

• Connecting to Web-Hosted Data Files

• Connecting to HTML Web Pages

• Connecting to Pages Without Tables

• Caveats and Frustrations with the Web Experience

Unit 12 - Relational Data Sources

• Connecting to Databases

• Query Folding

• Data Privacy Levels

• Optimization

Unit 13 - Reshaping Tabular Data

• Complex Pivoting Patterns

• Complex Unpivoting Patterns

• Advanced Grouping Techniques

Unit 14 - Conditional Logic in Power Query

• Conditional Logic Basics

• Creating Manual IF() Tests

• Replicating Excel’s IFERROR()

• Function

• Working with Multiple Conditions

• Compare Against Next/Previous Row

• Columns From Example

Unit 15 - Power Query Values

• Types of Values in Power Query

• Tables

• Lists

• Records

• Values

• Binaries

• Errors

• Functions

• Keywords in Power Query

Unit 16 - Understanding the M Language

• M Query Structure

• Understanding Query Evaluation

• Iterators (Row by Row Evaluation)

• Other techniques

Unit 17 - Parameters and Custom Functions

• Building a Custom Function Using Parameters

• Building a Custom Function Manually

• Dynamic Parameter Tables

• Implications of Parameter Tables

Unit 18 - Date and Time Techniques

• Generating Calendar Boundaries

• Calendars with Consecutive Dates

• Filling Specific Date/Time Ranges

• Allocations Based on Date Tables

Unit 19 - Query Optimization

• Optimizing Power Query Settings

• Leveraging Buffer Functions

• Reducing Development Lag

Unit 20 - Automating Refresh

• Options for Automating Refresh in

• Excel

• Automating Query Refresh with VBA in Excel

• Scheduling Refresh in Power BI

The audience for this course is data professionals and business intelligence professionals who want to learn how to extract, transform and load data using Power Query to perform data analysis with various software (Excel, Power BI Desktop). This course is also targeted toward those individuals who develop reports that visualize data from the data platform technologies that exist on both in the cloud and on-premises.

Very Good knowledge of Microsoft Excel.

The course is 100% practical. The topics are delivered with short presentations by the instructor followed by a  step-by-step demonstration by the instructor and repetition by the students, examples and discussions on how a feature may be used with real life examples and practice through written exercises.