CFI - Power Query Fundamentals 

Course Introduction

Introduction to Power Query

Course Outline

Download Power Query

Overview of Course Files

Basic Transformations Objectives

What is a CSV File?

Database Normalization

Basic CSV Unpivot

Importing CSV file 1a

The Power Query Editor

Manipulating Column Headers

The Query Steps Pane

Deleting Columns

Unpivoting Columns

Filtering Blanks

Defining Data Types

Loading Query 1a to the Worksheet

CSV Import N Columns

Duplicating Queries

Modifying the Source

Importing an Unknown Number of Columns

Futureproofing Our Query

Filter Non-dates

Import CSV File 1c

Identify Column Headers

Remove Unwanted Columns

Unpivot and Rename

Date Settings

Interpreting and Filtering Dates

Loading as Connection Only

Grouped Row Headers

Fill Values Down

Thinking about the Order of Steps

Pivot Grouped Headers

Adding a Duplicate Column

Finishing Up

Learner Exercise

Clues

Review

Exercise 1z

Extracting Information

A Closer Look at Filters

Creating Query Folders

Better Filters

Extracting Characters from Text

Checking Filter Logic

Import Basic Excel Files

Dealing with Ghost Columns

Splitting Columns

Referenced Queries

Splitting by Delimiter

Splitting by Character Number

An Introduction to M Code

Commenting Code

Exercise 2d - Splitting Columns into Rows

Importing an Excel Table

Split a Column into Rows

Learner Exercise

Exercise 2z - Clues

Exercise 2z - Review

What if the Dataset Grows

Exercise 2z

Consolidating Data Objectives

Exercise 3a - Grouping Data

Setting Up Our Query

Aggregating Multiple Columns

Identifying a Specific Month

Grouping Rows

Merging Tables

Two Queries from the Same File

Merging Queries

Expanding Merged Columns

Exercise 3c - Combining Identical Files

Create Query from Folder

Modifications Required before Combining

The Combined Query

Extracting Data from Each Filename

Using Filters for Investigation

Exercise 3z - Student Exercise

Clues and Challenges

Review

Exercise 3z

Dealing with Errors Objectives

Basic Errors

Tracking Down Errors

Managing Errors in the Power Query Editor

Using Find Replace to Fix a Character Error

Errors with Date Locales

Changing a Column Date Locale

Dealing with Exceptions

Creating a Conditional Column with a Columnar Output

Creating a Conditional Column with a Text Output

Errors with File Locations

Creating a Parameter

Using a File Location Parameter

Opening the All Queries File

Student Exercise

Clues

Review

Course Summary

Exercise 4z