Skip to content
Dashboard
Menu
Dashboard
Courses
Discussions
My Account
Contact Us
Course Overview
Power Query Academy
Course Content
Expand All
Welcome to Power Query Academy
1 Lesson
Expand
Module Content
0% Complete
0/1 Steps
Getting Started
Introduction to Power Query
5 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/5 Steps
What is Power Query?
Versions of Power Query
Power Query UI Walk Through
When to Use Power Query
Our Suggested Learning Path
Module Quiz – Introduction (Academy)
Importing Basic Data
4 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/4 Steps
Connecting to Delimited Flat Files
Connecting to Data in Excel Workbooks
Connecting to Data in the Current Excel Workbook
Connecting to Non-Delimited Flat Files
Module Quiz – Importing Basic Data (Academy)
Query Settings
6 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/6 Steps
Step Settings
The Get Data User Experience
Data Profiling and Data Quality
Configuring Query Load Destinations
Query Error Auditing
The Importance of Defining Data Types
Module Quiz – Query Settings (Academy)
Simple Transformation Techniques
11 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/11 Steps
Unpivoting Columns
Pivoting Columns
Splitting Columns
Filtering Data
Sorting Data
Working with Existing Columns
Adding a Column Based on a Cell Value
Working with Random Numbers
Grouping and Summarizing Data
Converting a Flat Table into Dimensions
Ranking Data
Module Quiz – Simple Transformation Techniques
Appending Data
7 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/7 Steps
Appending Tables Individually
Appending Tables in the Current Workbook
Appending Worksheets in an External Workbook
Combine all Flat Files in a Folder
Combine a Named Worksheet from Multiple Workbooks
Combine All Worksheets in Multiple Workbooks
Loading the Latest Version of a Workbook
Module Quiz – Appending Data
Merging Data
9 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/9 Steps
Merging Basics
Join Types
Cartesian Products
Approximate Match
Fuzzy Matching Basics
Fuzzy Matching in Data Quality
Changing Data and Adding Content
Cleanse Multiple Characters in Data
Merging for Data Auditing
Module Quiz – Merging Data
Web Based Data Sources
5 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/5 Steps
Basic “From Web” Concepts
Web Hosted Files
Web Pages with Table Tags
Inferred Tables
Tables from Example
Module Quiz – Web Based Data Sources
Miscellaneous Data Sources
7 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/7 Steps
Getting Data from Exchange
SharePoint Data: Introduction
SharePoint Data: Lists
SharePoint Data: All Files From Site
SharePoint Data: From Folder Experience
Importing Data from a JSON File
Connecting to a PDF File (Updated March 2024)
Module Quiz – Miscellaneous Data Sources
Data From Relational Sources
8 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/8 Steps
Connecting an Access Database
Connecting to SQL Server Databases
SQL Databases With Defined Relationships
SQL Databases Without Defined Relationships
Understanding Query Folding
Parameterized Queries in SQL Server
Connecting to SQL Server Analysis Services
Direct Connectivity in Power BI Desktop
Module Quiz – Data from Relational Sources
Introduction to Conditional Logic
2 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/2 Steps
Conditional Logic Basics
Creating Columns from Examples
Module Quiz – Introduction to Conditional Logic
Practice Labs
3 Lessons
Expand
Module Content
0% Complete
0/3 Steps
Transforming a Stockflow Report into a Data Source
Merging Data from Several Sources
Grouping Data Sets
Advanced Data Reshaping
10 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/10 Steps
Time to Change Gears
Pivoting Stacked Data
Pivoting Stacked Data Revisited
Unpivoting Subcategorized Data
Pivoting Vertical Pairs
Pivoting Horizontal Pairs
Numbering Grouped Rows
A Hacker’s Approach to Numbering Group Rows
Adding Comments to Tables
Remove Top x (Dynamic) Rows
Module Quiz – Advanced Data Reshaping
Managing Queries
6 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/6 Steps
Automating Refresh in Excel
Sharing Queries
Query Organization
Bonus Tips for Working With Queries
Using Relative References in Queries
Repointing a Query to a Different Data Source
Module Quiz – Managing Queries
Formulas and Conditional Logic
7 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/7 Steps
Using Power Query Formulas
Conditional Logic – Manual IF Tests
Conditional Logic – Handling Errors
Catching and Targeting Errors
Conditional Logic – Working with Multiple Conditions
Conditional Logic Against the Next or Previous Row
Real World Example of Previous Row Logic (Repeat Ticket)
Module Quiz – Formulas and Conditional Logic
M Deep Dive
7 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/7 Steps
Power Query Values
Power Query and M Syntax
M Functions – Integrated Samples and Documentation
Current Row Context
Navigation or Drill-Down
Tokens – Identifiers, Keywords, Literals, and Punctuation
Advanced Row Context
Module Quiz – M Deep Dive
Parameters and Custom Functions
4 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/4 Steps
Manual Creation of Custom Functions
Debugging Custom Functions
UI Assisted Parameters and Functions
Parameter Tables – Extracting a Single Value
Module Quiz – Parameters and Custom Functions
Advanced M Techniques
6 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/6 Steps
Dynamically Creating a List of Headers
Techniques to Rename Columns
Advanced Table Append
Managing Changes to Columns
Creating Audit Queries and Alerts
List of Values to Filter Columns
Module Quiz – Advanced M Techniques
Advanced Web Techniques
5 Lessons
Expand
Module Content
0% Complete
0/5 Steps
Parsing an HTML Web Page
Parsing HTML as Text
Basic Web Scraping (Updated March 2024)
Combining Multiple Web Pages
Pagination and DO/WHILE
Date and Time Techniques
10 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/10 Steps
What is a Calendar Table?
Basic Calendar Tables – From Start Date to End Date
Basic Calendar Tables – From Start Date + x Days
Extending Calendar Table Columns
Power BI Calendar Table and Some Tricks
Convert Calendar Year to Fin Year
Convert UTC to Local DateTimes
Switching Time Zones
Convert UNIX Timestamp to Date
Convert Julian to Gregorian Dates
Module Quiz – Date and Time Techniques
Allocating Values to Periods
10 Lessons
Expand
Module Content
0% Complete
0/10 Steps
Allocation Summary
Evenly To Each Day in Period
To Month by Days in Period
Evenly to Each Month in Period
To Year by Days in Period
Evenly to Each Year in Period
Over x Days From Start Date
Over x Months from Start Date
Over x Years From Start Date
Over x Instances at y Intervals From Start Date
Query Optimization
3 Lessons
|
1 Quiz
Expand
Module Content
0% Complete
0/3 Steps
Optimizing Power Query Settings
Leveraging Buffer Functions
Reducing Development Lag
Module Quiz – Query Optimization
Dataflows (Power Query Online)
8 Lessons
Expand
Module Content
0% Complete
0/8 Steps
Background to Dataflows
Introduction to Dataflows
Demo: Creating a Dataflow
Demo: Using a Dataflow
Computed Tables
Power Query and Dataflows
Taking Control of a Dataflow
Dataflows Summary
Bringing it All Together
4 Lessons
Expand
Module Content
0% Complete
0/4 Steps
Correcting Incorrect Records
Toggle File Combine Between Local and SharePoint
Flatten Absence Entries: A Tricky Example
Real World Extracting Web Data
Practice Labs
3 Lessons
Expand
Module Content
0% Complete
0/3 Steps
Unpivoting Stacked Tables with Multiple Columns
Consolidating Excel Files
Building Portable File Consolidation Solutions
Rate the Course
1 Lesson
Expand
Module Content
0% Complete
0/1 Steps
Course Feedback