Go back to Courses

Master Your Data

  • On-Demand
Author: Ken Puls
If you need to learn one skill in Excel today, how to work with Power Query is it. Power Query will change the way you "Get & Transform" data in Excel, allowing you to clean up, reshape, and combine your data with ease, no matter where it comes from.
Course

Master Your Data

$250 Renews at $29 per year
(All prices in USD)
This Course contains:
8 hours of training content
13 Content Modules
Downloadable files and Handouts
Support and Discussion groups
Certificate of completion
DURATION:
8 hours
TOOLS:
Excel
SKILLS:
Data Preparation

What you'll learn?

Importing data from text files and web pages
Appending data from one table to another
Merging two data sets together
Importing all files in a folder in one shot
Unpivoting tables with ease
Creating dashboards from email in minutes

Course Overview

Course Description

The sad reality is that not all data is stored in nicely curated databases and often – even when it is – the data analyst doesn’t have access. Instead we have to piece together data provided in text files, Excel files, web pages and even the body of emails to build the business intelligence solutions we need. Until now this has been a painful process with a great deal of cut and paste effort, and updates have been tedious and error prone. That stops today.

In this course, you’ll learn how Power Query can clean up, reshape and combine your data with ease. Converting ASCII files into tables, combining multiple text files in one shot and even un-pivoting data is not only simple, but an investment in the future, refreshable with a single click when next needed.

If you need to learn one skill in Excel today, how to work with Power Query is it.  Not only will it change the way you “Get & Transform” data in Excel, but it’s also the system used to collect data for Power BI, meaning these skills are transferable to other programs.

Who this course is for?

  • Anyone who needs to pull data into Excel, clean it up and/or consolidate it.
  • Experience working with Pivot Tables is an asset, but not required.

Course Formats

On-Demand
This is an on-demand course, delivered via pre-recorded video modules that you can consume at your own pace. It includes all required sample files and practice sets.

Course Content

Platform Introduction
3 Lessons
Course Examples
Course Introduction
Who’s Ken
Pre-work – PivotTable Review
1 Lesson
Essential PivotTable Skills
The Trouble with Data
5 Lessons
Building Modern BI Solutions
The Trouble with Data
Classic Excel Cleanup
Power Query Adds ETL to Excel
Dealing with Data in Modern Excel
Power Query Basics
2 Lessons
Importing a CSV File
Update Options
Appending Data
8 Lessons
Basic Append Operations
Importing a Folder of Files – Overview
Importing a Folder of Files – Step 0
Importing a Folder of Files – Step 1
Importing a Folder of Files – Step 2
Importing a Folder of Files – Step 3
Importing a Folder of Files – Step 4
Update Options Revisited
Merging Data
4 Lessons
Merging Tables with Power Query
Watch
7 Ways to Join Your Data
Cartesian Products
Merging with an Approximate Match
Reshaping Tabular Data
5 Lessons
Unpivot With Ease
Watch
Unpivot Subcategorized Tables
Pivoting Stacked Data
Pivoting Multi Column Vertical Pairs
Pivoting Multi Column Horizontal Pairs
Grouping Data
2 Lessons
Data Grouping Recipe
Calculating Percentage of Total via Grouping
Conditional Logic
3 Lessons
Creating Conditional Logic
Conditional Logic – Advanced
Columns From Example
Performance
3 Lessons
Dealing with BIG Data – A Case Study
What is Query Folding?
How to Tell if Query Folding is Active
Awareness Topics
3 Lessons
Reusing Import Queries
Dynamic File Paths
The Impact of Power Query
Help Resources
6 Lessons
Free Help Forums
Power Query Articles
Power Query Books
Power Query Recipes
Power Query Academy
Free Newsletters

How subscription works?

This course is built on an annual subscription format. Your registration comes with 12 months of access to the course materials, so you can refer back to the videos and example files whenever you like. Your subscription also gives you access to our private Discussion Forum, where you can ask questions related to the course materials, as well as how to apply those teachings to your own work. This forum is an invaluable resource that also allows students to practice their own learnings by helping others with those questions.

On your annual renewal date, your subscription will be automatically renewed with the credit card information on file, unless you cancel your subscription beforehand. You will be emailed a renewal reminder about one month before the renewal date. Please see the FAQs below for information about subscription renewals and cancellations.

About the author

Ken Puls

Microsoft MVP, FCPA, FCMA
Ken Puls is one of the founding partners of Skillwave.Training, and the President and Chief Training Officer of Excelguru Consulting Inc. in Canada. He is a Chartered Professional Accountant, blogger, author, and trainer with over 20 years of business and IT experience. His passion lies in exploring tools to turn data into information, and teaching others how to use them. Ken is a Microsoft MVP and a leading expert in Excel, Power Query, and Power BI.

FAQ

How do I access the course materials? Are they downloadable?
The videos are streamed on-demand and may be watched as often as you like. However, they are not downloadable. The example files and handouts for each lesson are downloadable.
Where are the course videos hosted?
All the videos for the course are hosted on Vimeo. Please note that some networks block access to Vimeo, in which case the videos will not be watchable. You may want to double-check that your network allows access to Vimeo before registering in the Academy, to ensure that you will be able to see the videos.
How do I receive my Certificate of Completion?
After all the modules and lessons have been completed, you will be able to download the Certificate of Completion from the My Courses area of your Account Dashboard.

Reviews

Master Your Data

0
(0 Review)
5
4
3
2
1

Reviews

There are no reviews yet.

Only verified customers who have purchased this product may leave a review.