Revealed: Power Query Advanced Tips And Tricks

Name: Revealed: Power Query Advanced Tips And Tricks
Rating: 4.1 (118 reviews)

Discover The Power Query Advanced Secrets Microsoft Was Supposed To Show You, But Never Did

Created byDoug Burke

Last updated 2/2019

English

English [Auto],

What you'll learn

Create data analytics like world-class companies
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
Write If-Then-Else statements within conditional columns and create embedded functions in custom columns
Reference the Power Query Formula Language to find and use unique functions to transform your data
Build using the 'M' language Advanced Editor and Query Manager to control your processes and work faster

Course content

9 sections • 37 lectures • 2h 55m total length

Welcome to the fun1:11
Discover the data transformation power of Power Query and Power BI, from Excel to business intelligence, and boost job security through analytics skills.
Data career thoughts2:02
Stay ahead in a rapidly changing job market by building data skills and mastering Power Query for Excel to transform data and make better business decisions.
My Power Query beliefs0:59
Discover Power Query principles and time-saving data analytics strategies that empower you to work faster. Advance your career with data management skills within everyone's reach.
A few words about this course1:27
Master Power Query advanced tips by creating conditional and custom columns, learning the formula language called AM and its functions, and using the advanced editor to reuse code across workbooks.
All about the exercise files0:41
Access and use the complete exercise files to learn Power Query, including downloadable Excel files with transformations and text files with ready-to-copy code for hands-on practice.

Conditional & custom columns overview1:56
Explore how conditional and custom columns clean and extend data in Power Query by applying rules, creating derived values, and embedding formulas for faster, multi-step transformations.
Conditional columns basics7:55
Explore conditional columns in Power Query, learning to create new columns with if-then-else rules, manage data types, and transform datasets for cleaner results.
Conditional columns - training exercise2:34
Explore conditional columns in Power Query with a four-by-four dataset, creating if-then-else rules and custom columns to clean and query source data.
Conditional columns - training exercise solution9:26
Create and apply conditional columns in Power Query to categorize data by age, animal type, and text patterns, follow exercises, and learn how to clean and transform data for analysis.
Custom columns basics6:11
Create a custom column in Power Query using the formula window and reference fields, with text in quotes and numbers unquoted. Note lowercase if-then-else and understand case sensitivity.
Custom columns - training exercise1:53
Custom columns - training exercise solution9:49
Differences and similarities (conditional and custom columns)3:01
Explore the differences and similarities between conditional and custom columns for Power Query, and learn when formulas or the column from examples feature best shape data.

Good - Manual queries3:42
Explore manual and waterfall Power Query methods to convert dates to year text with a prefix, using step-by-step editor formulas and time-travel-like rollback.
Manual queries - training exercise4:50
Manual queries - training exercise solution (1 of 2)10:00
Use power query to transform data: split columns to isolate dog or cat, filter out squirrels, remove columns, and group by to compute the average age for dogs and cats.
Manual queries - training exercise solution (2 of 2)11:31

Functions and formulas4:04
Master functions and formulas in the Power Query language to transform data with custom columns, and find key functions via online reference, a reference sheet, or the formula bar.
Finding functions #1 - Power Query formula reference5:42
Finding functions #2 - reference sheet5:21
Discover how to speed Power Query tasks with a personal reference sheet of common formulas, including date to text, date month, and text upper, plus quick example workflows.
Finding functions #3a - formula bar (=#shared)2:23
Find functions quickly in power query using the formula bar, equals pound shared, and a simple three-step process to convert results into a table for building custom columns.
Finding functions #3b - custom query4:24
Explore creating and filtering Power Query custom queries: open a blank query, use formula reference, convert records to tables, apply text and date filters, and save as table or connection.
Finding functions #3c - follow along6:05
Learn to use Power Query functions to create custom columns that transform text, such as uppercase, and extract the first letter with Text.Start, tracking changes via applied steps.
Finding functions #3c - share across workbooks5:28
Discover how to copy Power Query functions across workbooks using the advanced editor, create new blank queries, paste, rename, and load as a connection.

Waterfall queries4:49
Master waterfall queries by embedding multiple functions in a single custom column to convert a date into a birth year with a C-y prefix, reducing steps and enabling easier debugging.
Waterfall queries - more examples9:38
Explore advanced Power Query techniques with waterfall queries: convert dates to text, create custom columns, merge and concatenate fields, and optimize formulas for one-step results.

How to read 'M' code4:56
Formulas follow a standard pattern
Understand the pattern to quickly modify and imbed
Rename your steps to simplify 'M' code5:10
Add comments to your 'M' code4:56
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
Query Manager in Query Editor8:56
Use to move, rename, create groups, move groups
It's better and quicker within Query Editor compared to main Excel ribbon

Part 1 - Clean Your Data3:25
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
Part 1 - Clean Your Data Solution13:28
This video contains a detailed example of how I used Power Query to create the data table.
Part 2 - Analyze Data
Part 3 - Clean Up Query

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

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.

By taking this course, you will say "Wow. It didn't know I could do that with Power Query!"

Who this course is for:

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

Revealed: Power Query Advanced Tips And Tricks

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 6min

How To Get The Most From This Course3 lectures • 5min

How to Use Power Query: Do More In Fewer Steps2 lectures • 3min

How to Use Conditional & Custom Columns8 lectures • 43min

How to Use Manual Queries4 lectures • 30min

How to Use Advanced Queries7 lectures • 33min

How to Use Waterfall Queries2 lectures • 14min

How to Take Advantage of 'M' Code4 lectures • 24min

Case Study (Assignments)2 lectures • 17min

Requirements

Description

Who this course is for: