Even if you prefer to stay low-key, your newfound Power Query skills will speak volumes. You’ll naturally become known as the go-to problem-solver in your office, a reliable expert whom colleagues admire and turn to for solutions.
File Size: 6.11 GB.
Leila Gharani – Master Excel Power Query – Beginner to Pro (including M)
BECOME THE EXCEL HERO WITH POWER QUERY
Discover the smarter, faster way to handle data with our Power Query course. You’ll be able to turn every data task into a chance to stand out. Become the go-to Excel problem-solver at work with this essential skill.
This course is for everyone, no matter what job you do. We’ll start from scratch and before you know it, you’ll become a Power Query expert.
Think of this course as your all-in-one solution. While you might find snippets of Power Query knowledge on YouTube, they fall short. Ours is comprehensive, covering niche, real-world topics that free content usually doesn’t touch. It’s about making your work life not only easier but also more enjoyable.
What you’ll achieve with our Power Query Course
Effortless Efficiency!
Yes, efficiency can be effortless once you discover a new way of handling your data. The only regret? Not discovering this sooner – which is what we hear the most from our members. But remember the saying: The best time to get started was yesterday. The second best time is today.
Become the Office Star
Even if you prefer to stay low-key, your newfound Power Query skills will speak volumes. You’ll naturally become known as the go-to problem-solver in your office, a reliable expert whom colleagues admire and turn to for solutions.
Master Problem-Solving Techniques
Start using outside-the-box techniques to tackle Excel challenges. Whether it’s combining data from various sources, importing from PDFs or the web, or tidying up messy data filled with spaces, blank cells or messed up columns, this course will teach you smarter techniques, saving you time and energy.
How Power Query Can Change Your Life
Make Your Excel Life Easier with this Power Query Training
Inside your Microsoft Excel window sits a magic button.
It’s not called “Power Query” – instead it’s called “Get Data” (in the Data tab).
Your data could be ANYWHERE. It could be sitting right there on the sheet you’re on. It could be in another Excel file, in a text file, on the web or in a PDF document.
The instant you click this button, a new world opens up, helping you to quickly complete tasks – whether it’s cleaning up mixed-up rows or messy columns, combining data with other files or anything else you need done.
Once you know your way a round – even just a little bit – you get to do things that only Excel gurus used to be able to do.
Now these advanced techniques are just a “click” away for you.
They can save you from pulling your hair out as you clean data.
You can replace hours and hours of manual labor.
And you can ship reports to your boss and colleagues faster – winning you praise and promotions.
The best part: Power Query remembers your steps!
Next time you need to do the same thing, you just open your file and click “Refresh” – then sit back and enjoy the show (i.e. watch the magic happen as your data updates automatically).
Let me give you more details – let’s visit a typical day in the life of an Excel user.
Start using Excel in the Power Mode. Just by finishing the FIRST hour, you’ll be amazed at what you can achieve with Power Query. It will give you tons of ideas on how to make your work easier.
Here’s Your Learning Journey
Leila Gharani introduces you step-by-step to Excel’s biggest time-savings feature.
You’ll be up and running with Power Query in just an hour. You can view the detailed curriculum below.
See anything that jumps out? Which lesson do you think you’d begin with?
Section 1: Introduction
Applications of Power Query
Important: Excel Versions, Expectations & Course Pathways
(For older Excel versions) Workaround for Loading as Pivot Table
DOWNLOAD ALL Files & How to Use Them
Quick Check-in
Section 2: Power Query Essentials
What You Can Achieve by the End
What is a Proper Data Set?
(In case you need to) Links to Brush up Your Excel)
Section 3: The Power of Power Query
Analyze Large Data Quickly! Pivot Table or Power Query?
Power Query Overview: Import Large Data from Another File
Power Query Editor & Basic Transformation
Quick Insights on Data Quality & Distribution (Hidden Feature)
Formula Bar, Applied Steps & M Code
Close & Load Destinations (How to Change Them)
Refresh Data & PQ Refresh Options
Import CSV File & Extract Text Based on Pattern
Merge Data with Another File (Pivot Table from Multiple Files)
Old School Method: What if There Was NO Power Query?
Activity: Get Familiar with PQ
Section 4: Important Power Query Tips & Tricks
Uploading Data From Excel
The Hidden Table Method (Hidden Feature)
Handling Changes to Source
Data Types
Data Types vs. Formatting & Null Values
Power Query Navigation Shortcuts (Become Pro)
Finding & Correcting Errors in Data
More Data Views: Duplicate OR Reference Query?
Keeping an Eye on Query Dependencies
Delete, Manage, Copy Queries & Backup Results (Become Pro)
CHALLENGE – Find and Correct the Mistake
QUIZ – Power Query Tips & Tricks
Best Practice for Power Query
Section 5: Helpful Power Query Transformations
Text Transformations (Format, Extract & more)
Merging Columns & What to Watch Out For
Fill & Replace Values to Create Proper Datasets
Sort Data including Multiple Levels
Remove Duplicates including Multiple Columns
Number Transformations & What to Watch Out For
Working with Filter (AND & OR Conditions)
CHALLENGE – Can you Find the Mistake? Problem with Filter
Change Type & Remove Columns Trap
Key Takeaways – Helpful Power Query Transformations
Section 6: Powerful Power Query Transformations
Column From Examples – Extract Patterns Quickly
Allocate Data to Groups or Buckets (Become Pro)
Conditional Columns in Power Query
Aggregating (Grouping) Data on Multiple Levels
Group By for All Rows (Become Pro)
Unpivot Columns – Basics
Unpivot & How to Overcome Common Errors
Pivot Columns – Basics
Problem with Split by Delimiter (that’s easy to miss)
Split Column by Rows instead of Columns
CHALLENGE – Summarize Sales By State
Solution: Summarize Sales By State (Bonus Map Chart Included)
Section 7: Date & Time Transformations
Date Transformations (Extract Age, Weekday etc.)
Creating Dates from Text or Columns (Become Pro)
Time Transformations (Calculating Hours worked)
Date & Number Errors When Importing Data (Locale)
CHALLENGE – Pivot Table for Hours Worked by Month & Week Number
Solution: Pivot Table for Hours Worked by Month & Week Number
QUIZ – Date & Time Transformations
Section 8: Custom Column & Basic M Manipulation
Important Basic Power Query M Logic
Why Use Custom Columns
Introduction to “Add Custom Column”
Custom Columns Type Compatibility & Intrinsic Functions
Skipping Steps in Power Query (Become Pro)
Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
Drill-Down in Power Query
Custom Formulas for Template Creation (Hidden Tip)
QUIZ – Custom Columns and Basic M
Key Takeaways – M Basics
Section 9: Power Query Data Sources (Online)
Connecting to different Sources
Import Data from a Website
Automatically Connect to Files on Websites (Hidden Feature)
Import Data from ODATA
Get Google Sheet Data with Power Query
Connect to Outlook Online (Microsoft Exchange)
Connect to SharePoint or OneDrive for Business
Pro Tip: How to Change Source from Local to SharePoint
CHALLENGE – Google Sheets Survey “Your Dinner Plans”
BONUS FREE LIBRARY & NEW Custom Data Types
Section 10: Combining / Appending Data
Why Append Data? The Difference Between Merge & Append
Combine / Append Data from Multiple Workbooks
Combine All Files in a Folder (with Excel Tables)
Combine All Files in a Folder (Without Excel Tables)
How to Adjust Folder Path from Local to SharePoint Drive
Combine All Sheets in a File (Pivot Table from Multiple Sheets)
Overcome Potential Errors when Combining Sheets (Become Pro)
Consolidate Data from Multiple Sheets in the Current Workbook
CHALLENGE – Consolidate Data from Folder (with a twist)
Solution: Consolidate Data from Folder (with a twist)
Tips & Key Takeaways When Appending in Power Query
Section 11: PROJECT – Report on HR Data
Test Your PQ Knowledge by Completing This Project
Calculate Years Worked in Each Position
Calculate Years Worked in the Company
Split First, Last & Middle Names to Separate Columns
Key Takeaways – Data Analysis & Preparation with Power Query
Section 12: All Merge Options – Join Kind Options Explained
Overview of Merge Options and Join Kinds
Left Outer Join & Right Outer Join
Merge Based on Multiple Columns (Become Pro)
Can You Find the Mistake? Merging Text Columns
Merge Data to Get Multiple Match Results & Bonus Tip
Inner & Full Join in Power Query
Left & Right Anti Join when Merging in Power Query
How to Use Fuzzy Match in Power Query
Fuzzy Match with Transformation Table (Hidden Feature)
CHALLENGE – Someone Hacked the Salary File – Find Mismatches in Data
Solution: Someone Hacked the Salary File – Find Mismatches in Data
Key Takeaways – Power Query Merge Kinds
Section 13: When to Use Power Pivot & Load to Data Model
When to Load Data to the Data Model
Availability of Power Pivot
Pivot Table from Multiple Excel Tables
Power Pivot Table with Data Model & Power Query
Create a Calendar Table in Power Pivot
Pivot Slicers & TimeLine with Power Pivot & Power Query (Become Pro)
Key Takeaways – Data Model & Power Query
Section 14: Power Query Advanced to Pro
What You Can Achieve as an Advanced Power Query User
You’ve Come So Far!
Section 15: Solving Complex Data Analysis Problems with Merge
Learning Objectives for this Section
Messy Data from Multiple Rows to One Row (Create Proper Data Set)
Search and Replace Bulk Values
Calculate Value Difference to Previous Row
Approximate Match Lookup with Merge
CHALLENGE: Create a Report based on Multiple Parameters
Solution: Create a Report based on Multiple Parameters
Section 16: Advanced Problem Solving using the Power Query Interface
Learning Objectives for this Section
Assign Unique Number to Group
Advanced Unpivot Techniques
Advanced Pivot Techniques
Incremental Data Load & Self Referencing Query (Hidden Tip)
Key Takeaways: Advanced Problem Solving
Section 17: PROJECT – Create a Modern Excel Dashboard with Power Query & Pivot Tables
Excel Dashboard Project Overview
Prepare a Draft of the Dashboard
Import Master data from External Workbook with Power Query
Import Data from Text File with Power Query
Create the Data Model & Define Relationships in Power Pivot
Create Logic for Latest and Previous Month in Power Query
Setup Calculations with Pivot Tables for Latest Month
Link Excel Shapes to Data & Linked Picture Trick
Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table)
Linked Table for Sales by Product Category
Excel Pivot Chart for Monthly Sales
Pivot Slicer Connected to Multiple Pivot Tables
Finalize the Excel Dashboard
Key Takeaways: Excel Dashboard Project
Section 18: Understanding M Formula Language (Let, Lists, Records & Improving Performance)
M Language – How M Thinks (let Expression & Values)
Defining & Invoking Custom M Functions
Reference Guide for Standard M Functions
Lists & Records and how to Reference a Specific Cell in a Table
Summarizing Brackets & Lookup Operators in M code
Creating Lists and Tables inside Power Query
Understand Each Keyword & the Purpose of _ (underscore)
Using Power Query Parameters
IF Then & Lookup Operators to Lookup Values in Previous Row
Error Handling – Bulk Replace Lookup with Try Otherwise
Speed up Queries: Table.Buffer & How to Test Impact (Hidden Tip)
Query Folding – Improve Performance for Relational Databases
QUIZ: Test Your Knowledge – Understanding M
Key Takeaways & Power Query M Formula Language Specifications
Section 19: Working With List & Table Functions
What You Learn in this Section
Create a Calendar Table From Start to End Date
Power Query Text Functions (Text.Contains, Text.Replace)
How to Use List Functions in Custom Column
Merge Values from Rows into One Cell
Section 20: Creating & Invoking Custom Functions
What You Learn in this Section
Unpivot & Consolidate Data From Multiple Sheets (with Custom Function)
Rank by Group (Nested Rank) with Power Query
Running totals by Month (Invoking functions)
How Far You’ve Come!
Section 21: Flexible Importing of Data with the Help of M Functions
What You Learn in this Section
Better Collaboration: File Path as Parameter in Power Query
Better Collaboration: File Path from a Cell (Formula.Firewall Error)
Import from Folder: Mixed Letter Case Headers & Trailing Spaces
Import All Sheets BUT Data Starts from Different Rows
CHALLENGE: Combine Sheets & Get Name from Cell Value
Solution: Combine Sheets & Get Name from Cell Value
Section 22: PROJECT – Power Query and Power BI
Project Overview of Power BI Dashboard
Install Power BI Desktop & Quick Overview
Upload & Transform Data with Power Query in Power BI
Setup Data Model & Create Relationships in Power BI
Create Your Dashboard & Reports within Minutes
Publish Dashboard to Power BI Service
Key Takeaways – Power Query & Power BI
Section 23: Final Words
Wrapping up & What’s Next
What You’ve Achieved!
It doesn’t matter if you’re a beginner, either..
- You don’t need to be an advanced Excel user
- You don’t need to understand “M Logic”
- You don’t need to be working with BIG data
- You don’t need to invest hours of learning Power Query before reaping the benefits
In fact, I give you this challenge…
Go through just a couple modules. Pick whichever ones jump out at you. Then, test your new skills at work. Give it an honest effort.
I promise your eyes will open to all the hours you’ve been wasting.
If you’re not impressed? We have a 14-day refund policy and will gladly return your enrollment fee.
But I don’t think that will happen, because once you get the hang of Power Query, you’ll never look back. Nobody does.
Course Features
- Lectures 0
- Quizzes 0
- Duration 10 weeks
- Skill level All levels
- Language English
- Students 195
- Assessments Yes
