Advanced Power Query - The Missing User Manual
4.3 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
26 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced Power Query - The Missing User Manual to your Wishlist.

Add to Wishlist

Advanced Power Query - The Missing User Manual

The course that every Power Query user needs. Build better transformations with confidence using higher-level features.
New
4.3 (2 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
26 students enrolled
Created by Doug Burke
Last updated 9/2017
English
Current price: $10 Original price: $120 Discount: 92% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3 hours on-demand video
  • 1 Article
  • 27 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
What Will I Learn?
  • Ever wonder how world-class companies create their data analytics? Advanced Power Query provides answers.
  • Executive Case Study: you will clean, analyze and document a project data set ; includes video instructions and downloadable Excel file solutions to insure you're learning
  • You will know the difference between conditional and custom columns and when to use each
  • You will write If-Then-Else statements within conditional columns and create embedded functions in custom columns
  • You will reference the Power Query Formula Language to find and use unique functions to transform your data
  • You will use the 'M' language Advanced Editor and Query Manager to control your processes and work faster
View Curriculum
Requirements
  • Strong desire to learn advanced Power Query data cleansing skills
  • Basic knowledge of Power Query (have used it, even at a beginner level)
  • Know your way around the Power Query ribbons and editor
Description

This course is essential for developing your data career

3rd in my series of Udemy courses on mastering data cleaning in the age of big data

Every day is a training day for your next job opportunity. Prepare by making data transformations that are simple to use and quick.  People notice, and you will have a solid career.  Microsoft has the right tool for this.  It’s called Excel Power Query.  Want to use it like a pro?

Advanced Excel Power Query Will Strengthen Your Data Analytics Career

  • 'Learn by doing' with downloadable exercise files, step-by-step video demonstrations and three-part 'Case Study' assignment that reinforces the knowledge you gained

  • Custom columns, the Power Query Formula Language (‘M’ code), embedded functions and much more

  • Dozens of tips & tricks focused on Power Query's best features

Control Your Data Instead Of Letting It Control You

I designed this course for data users of all levels.  There is something here for everyone.  We’ll solve some of the most common data cleansing problems encountered by data analysts.  You’ll walk away with first-rate knowledge  valued in the job market.  It's essential for developing your data career.

Companies are moving to Excel Power Query for next generation data analysis power. Let me prepare you for the career that companies need.

Who is the target audience?
  • Those who want to learn an in-demand business skill and increase their career opportunities
  • Those who want to improve their data analytics and Power Query talents
  • Those who are willing to try Power Query in new ways
Students Who Viewed This Course Also Viewed
Curriculum For This Course
37 Lectures
02:55:21
+
Introduction
5 Lectures 06:20

Data career thoughts
02:02

My Power Query beliefs
00:59

A few words about this course
01:27

All about the exercise files
00:41
+
How To Get The Most From This Course
3 Lectures 05:04

Versions of Excel that run Power Query
00:55

How To Install (Or Update) Power Query
03:04
+
How to Use Power Query: Do More In Fewer Steps
2 Lectures 02:30

Why fewer steps are better
00:55

How can I use Power Query most efficiently?

Quiz #1 - Steps and patterns
1 question
+
How to Use Conditional & Custom Columns
8 Lectures 42:45
Conditional & custom columns overview
01:56

Conditional columns basics
07:55

Conditional columns - training exercise
02:34

Conditional columns - training exercise solution
09:26


Custom columns - training exercise
01:53

Custom columns - training exercise solution
09:49

Differences and similarities (conditional and custom columns)
03:01
+
How to Use Manual Queries
4 Lectures 30:03
Good - Manual queries
03:42


Manual queries - training exercise solution (1 of 2)
10:00

Manual queries - training exercise solution (2 of 2)
11:31
+
How to Use Advanced Queries
7 Lectures 33:27
Functions and formulas
04:04

Finding functions #1 - Power Query formula reference
05:42

Finding functions #2 - reference sheet
05:21

Finding functions #3a - formula bar (=#shared)
02:23

Finding functions #3b - custom query
04:24

Finding functions #3c - follow along
06:05

+
How to Use Waterfall Queries
2 Lectures 14:27
Waterfall queries
04:49

Waterfall queries - more examples
09:38
+
How to Take Advantage of 'M' Code
4 Lectures 23:58
  • Formulas follow a standard pattern
  • Understand the pattern to quickly modify and imbed
How to read 'M' code
04:56

Rename your steps to simplify 'M' code
05:10

  • Editing code in the M window is difficult since PQ window does not have administrative features
  • I copy and paste into a text editor such as Notepad or Notepad++
  • This allows me to get a much better overall feel for what's going on
  • Can search and replace


Add comments to your 'M' code
04:56

  • Use to move, rename, create groups, move groups
  • It's better and quicker within Query Editor compared to main Excel ribbon
Query Manager in Query Editor
08:56
+
Case Study (Assignments)
2 Lectures 16:53

Scenario: You are the lead sales analyst for DogCatSquirrel Inc. and just received an Excel file containing sales results for FY17 Q1 (Jan, Feb, Mar).  

You must clean and analyze this data for two purposes:
1) bonus and commissions payments
2) to determine company performance during Q1

Note: to create 'State' column, use 

  • North = Michigan
  • South = Georgia
  • East = New York
  • West = Oregon
Preview 03:25

This video contains a detailed example of how I used Power Query to create the data table.

Part 1 - Clean Your Data Solution
13:28

Now that you have the 'Data Results' table, here are 6 questions to answer for the CFO. Each one should be answered using a different query created from the 'Data_Results' table.
Part 2 - Analyze Data
6 questions

True professionals always clean up after themselves. This includes simplifying names of 'Applied Steps' and adding some notes to the 'M' code (using Advanced Query Editor) to document what's been done. The 'Solutions' section contains a downloadable Excel file showing the completed assignment.
Part 3 - Clean Up Query
5 questions
About the Instructor
Doug Burke
4.3 Average rating
42 Reviews
1,407 Students
3 Courses
Data Systems Leader and Experienced Teacher

If you're like me, you see tremendous value in developing your career in data analytics, big data and business intelligence. Companies need these skills more than ever.

But did you know  . . . 

 ** The hardest part of any useful business intelligence system is getting clean data **

This statement comes from my 20+ years of experience building data analytics systems at Fortune 500 companies. Let me share my secrets so you get much better at cleaning your analytics data.

____________________________________________________________________

My specialty is creating clean data using Excel Power Query and Power BI Query Editor for analysis and visualizations.  

I love this topic . . . Learn from me.  

> I read the software user manuals so you don't have to

> I enjoy teaching complex topics in a clear, simple manner

> I'm very involved in the analytics community as a member of Microsoft's Power BI User Panel, and a frequent speaker at Oracle analytics events 

Courses like mine are not widely available so I decided to create one. Thanks to the internet, a global audience now shares this knowledge.

Take my courses and enter a community of like-minded people who want everyone to succeed.  I will personally answer any question posted and provide frequent updates on what's happening in the Power Query and Power BI communities.  There is so much value here and I encourage you to join.

____________________________________________________________________

** You can dramatically improve your data cleaning skills today ** 

What are you waiting for?