Autoplay
Autocomplete
Previous Lecture
Complete and Continue
Master Excel Power Query - Beginner to Pro (including M)
Section 1: Introduction
Applications of Power Query (5:55)
❗Important: Excel Versions, Expectations & Course Pathways (2:36)
(For older Excel versions) Workaround for Loading as Pivot Table
🔻DOWNLOAD ALL Files & How to Use Them
✍️ Quick Check-in
******* POWER QUERY ESSENTIALS *******
What You Can Achieve by the End (2:03)
What is a Proper Data Set? (4:15)
(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? (5:41)
Power Query Overview: Import Large Data from Another File (12:14)
Power Query Editor & Basic Transformation (9:43)
Quick Insights on Data Quality & Distribution (Hidden Feature) (6:04)
Formula Bar, Applied Steps & M Code (6:58)
Close & Load Destinations (How to Change Them) (5:50)
Refresh Data & PQ Refresh Options (4:21)
Import CSV File & Extract Text Based on Pattern (7:18)
Merge Data with Another File (Pivot Table from Multiple Files) (7:56)
Old School Method: What if There Was NO Power Query? (4:14)
Activity: Get Familiar with PQ
Section 4: Important Power Query Tips & Tricks
Feature Name Change in Office 365 in 2021
Uploading Data From Excel (8:17)
The Hidden Table Method (Hidden Feature) (4:23)
Handling Changes to Source (10:14)
Data Types (2:39)
Data Types vs. Formatting & Null Values (4:21)
Power Query Navigation Shortcuts (Become Pro) (3:49)
Finding & Correcting Errors in Data (5:01)
More Data Views: Duplicate OR Reference Query? (6:22)
Keeping an Eye on Query Dependencies (1:41)
Delete, Manage, Copy Queries & Backup Results (Become Pro) (4:04)
💪 CHALLENGE - Find and Correct the Mistake
🤔 QUIZ - Power Query Tips & Tricks
Best Practice for Power Query (4:54)
Section 5: Helpful Power Query Transformations
Text Transformations (Format, Extract & more) (9:18)
Merging Columns & What to Watch Out For (4:52)
Fill & Replace Values to Create Proper Datasets (7:35)
Sort Data including Multiple Levels (3:30)
Remove Duplicates including Multiple Columns (4:08)
Number Transformations & What to Watch Out For (8:48)
Working with Filter (AND & OR Conditions) (6:36)
💪 CHALLENGE - Can you Find the Mistake? Problem with Filter (Become Pro) (4:16)
Change Type & Remove Columns Trap (Become Pro) (7:19)
📝 Key Takeaways - Helpful Power Query Transformations
Section 6: Powerful Power Query Transformations
Column From Examples - Extract Patterns Quickly (9:40)
Allocate Data to Groups or Buckets (Become Pro) (5:33)
Conditional Columns in Power Query (5:28)
Aggregating (Grouping) Data on Multiple Levels (6:05)
Group By for All Rows (Become Pro) (6:59)
Unpivot Columns - Basics (6:20)
Unpivot & How to Overcome Common Errors (Become Pro) (6:43)
Pivot Columns - Basics (5:50)
Problem with Split by Delimiter (that's easy to miss) (5:38)
Split Column by Rows instead of Columns (Become Pro) (4:54)
💪 CHALLENGE - Summarize Sales By State
Solution: Summarize Sales By State (Bonus Map Chart Included) (7:59)
Section 7: Date & Time Transformations
Date Transformations (Extract Age, Weekday etc.) (7:05)
Creating Dates from Text or Columns (Become Pro) (5:14)
Time Transformations (Calculating Hours worked) (8:36)
Date & Number Errors When Importing Data (Locale) (6:27)
💪 CHALLENGE - Pivot Table for Hours Worked by Month & Week Number
Solution: Pivot Table for Hours Worked by Month & Week Number (8:18)
🤔 QUIZ - Date & Time Transformations
Section 8: Custom Column & Basic M Manipulation
Important Basic Power Query M Logic (6:26)
Why Use Custom Columns (3:38)
Introduction to "Add Custom Column" (6:05)
Custom Columns Type Compatibility & Intrinsic Functions (5:13)
Skipping Steps in Power Query (Become Pro) (4:54)
Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable (5:30)
Drill-Down in Power Query (3:52)
Custom Formulas for Template Creation (Hidden Tip) (5:42)
🤔 QUIZ - Custom Columns and Basic M
📝 Key Takeaways - M Basics
Section 9: Power Query Online Data Sources (Online)
Connecting to different Sources (1:54)
Import Data from a Website (9:51)
Automatically Connect to Files on Websites (Hidden Feature) (6:55)
Import Data from ODATA (3:13)
Get Google Sheet Data with Power Query (4:22)
Connect to Outlook Online (Microsoft Exchange) (4:00)
Connect to SharePoint or OneDrive for Business (2:31)
Pro Tip: How to Change Source from Local to SharePoint (4:02)
💪 CHALLENGE - Google Sheets Survey "Your Dinner Plans"
🎁 BONUS FREE LIBRARY & NEW Custom Data Types (10:16)
Section 10: Combining / Appending Data
Why Append Data? The Difference Between Merge & Append (2:30)
Combine / Append Data from Multiple Workbooks (10:57)
Combine All Files in a Folder (with Excel Tables) (11:27)
Combine All Files in a Folder (Without Excel Tables) (8:40)
How to Adjust Folder Path from Local to SharePoint Drive (5:18)
Combine All Sheets in a File (Pivot Table from Multiple Sheets)
Overcome Potential Errors when Combining Sheets (Become Pro) (7:10)
Consolidate Data from Multiple Sheets in the Current Workbook (7:57)
💪 CHALLENGE - Consolidate Data from Folder (with a twist)
Solution: Consolidate Data from Folder (with a twist) (8:12)
📝 Tips & Key Takeaways When Appending in Power Query
Section 11: PROJECT - Report on HR Data
Test Your PQ Knowledge by Completing This Project (4:15)
Calculate Years Worked in Each Position (8:06)
Calculate Years Worked in the Company (2:12)
Split First, Last & Middle Names to Separate Columns (5:20)
📝 Key Takeaways - Data Analysis & Preparation with Power Query
Section 12: Merge Options - Join Kind Options Explained
Overview of Merge Options and Join Kinds (6:19)
Left Outer Join & Right Outer Join (10:40)
Merge Based on Multiple Columns (Become Pro) (4:31)
Can You Find the Mistake? Merging Text Columns (4:47)
Merge Data to Get Multiple Match Results & Bonus Tip (6:10)
Inner & Full Join in Power Query (5:08)
Left & Right Anti Join when Merging in Power Query (2:53)
How to Use Fuzzy Match in Power Query (7:03)
Fuzzy Match with Transformation Table (Hidden Feature) (6:24)
💪 CHALLENGE - Someone Hacked the Salary File - Find Mismatches in Data
Solution: Someone Hacked the Salary File - Find Mismatches in Data (7:53)
📝 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 (5:27)
Availability of Power Pivot
Pivot Table from Multiple Excel Tables (10:42)
Power Pivot Table with Data Model & Power Query (8:33)
Create a Calendar Table in Power Pivot (5:00)
Pivot Slicers & TimeLine with Power Pivot & Power Query (Become Pro) (6:00)
📝 Key Takeaways - Data Model & Power Query
Master Power Pivot & DAX
******* POWER QUERY ADVANCED TO PRO *******
What You Can Achieve as an Advanced Power Query User (2:03)
🛣️ 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) (5:13)
Search and Replace Bulk Values (6:43)
Calculate Value Difference to Previous Row (7:00)
Approximate Match Lookup with Merge (9:01)
💪 CHALLENGE: Create a Report based on Multiple Parameters
Solution: Create a Report based on Multiple Parameters (7:31)
Section 16: Advanced Problem Solving using the Power Query Interface
Learning Objectives for this Section
Assign Unique Number to Group (6:13)
Advanced Unpivot Techniques (11:47)
Advanced Pivot Techniques (9:27)
Incremental Data Load & Self Referencing Query (Hidden Tip) (8:02)
📝 Key Takeaways: Advanced Problem Solving
Section 17: PROJECT: Create a Modern Excel Dashboard with Power Query & Pivot Tables
Excel Dashboard Project Overview (2:47)
Prepare a Draft of the Dashboard (3:47)
Import Master data from External Workbook with Power Query (5:33)
Import Data from Text File with Power Query (4:27)
Create the Data Model & Define Relationships in Power Pivot (3:08)
Create Logic for Latest and Previous Month in Power Query (7:42)
Setup Calculations with Pivot Tables for Latest Month (3:53)
Link Excel Shapes to Data & Linked Picture Trick (9:26)
Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table) (5:08)
Linked Table for Sales by Product Category (8:23)
Excel Pivot Chart for Monthly Sales (6:05)
Pivot Slicer Connected to Multiple Pivot Tables (12:14)
Finalize the Excel Dashboard (5:43)
📝 Key Takeaways: Excel Dashboard Project
Section 18: Understanding M Formula Language (Let, Lists, Records & Improving Performance)
M Language - How M Thinks (let Expression & Values) (14:51)
Defining & Invoking Custom M Functions (12:31)
Reference Guide for Standard M Functions (7:56)
Lists & Records and how to Reference a Specific Cell in a Table (8:46)
Summarizing Brackets & Lookup Operators in M code (4:30)
Creating Lists and Tables inside Power Query (9:33)
Understand Each Keyword & the Purpose of _ (underscore) (5:43)
Using Power Query Parameters (9:21)
IF Then & Lookup Operators to Lookup Values in Previous Row (7:24)
Error Handling - Bulk Replace Lookup with Try Otherwise (7:46)
Speed up Queries: Table.Buffer & How to Test Impact (Hidden Tip) (8:07)
Query Folding - Improve Performance for Relational Databases (5:23)
🤔 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 (10:18)
Power Query Text Functions (Text.Contains, Text.Replace) (11:49)
How to Use List Functions in Custom Column (10:21)
Merge Values from Rows into One Cell (6:08)
Section 20: Creating & Invoking Custom Functions
What You Learn in this Section
Unpivot & Consolidate Data From Multiple Sheets (with Custom Function) (11:03)
Rank by Group (Nested Rank) with Power Query (5:13)
Running totals by Month (Invoking functions) (10:15)
🛣️ How Far You've Come!
Section 21: Flexible Importing of Data with the Help of Functions
What You Learn in this Section
Better Collaboration: File Path as Parameter in Power Query (7:58)
Better Collaboration: File Path from a Cell (Formula.Firewall Error) (9:28)
Import from Folder: Mixed Letter Case Headers & Trailing Spaces (9:03)
Import All Sheets BUT Data Starts from Different Rows (6:13)
💪 CHALLENGE: Combine Sheets & Get Name from Cell Value
Solution: Combine Sheets & Get Name from Cell Value (13:23)
Section 22: PROJECT - Power Query and Power BI
Project Overview of Power BI Dashboard (2:31)
Install Power BI Desktop & Quick Overview (4:47)
Upload & Transform Data with Power Query in Power BI (8:49)
Setup Data Model & Create Relationships in Power BI (3:27)
Create Your Dashboard & Reports within Minutes (9:54)
Publish Dashboard to Power BI Service (5:26)
📝 Key Takeaways - Power Query & Power BI
Section 23: Final Words
🎆 Wrapping up & What's Next (2:12)
🛣️ What You've Achieved!
✍️ Leave a Review
🚩 Share Your Certificate
🎁 YOUR BONUS! Reference & Solution Books.
🔻DOWNLOAD Your 2 Complete eBooks
🎁 Power Query Pro Techniques
Introduction to Pro Techniques
🔻Download Pro Files
Pro 1️⃣ : More Advanced Trouble-Shooting with Power Query
Validation Report to Flag Errors (12:22)
Pivot Rows to Multiple Columns Based on Logic (8:07)
Split Column by Multiple Delimiters (Any Delimiter) (10:07)
Keep & Reorder Specified Columns (6:01)
Dynamically Rename Columns Based on List (7:46)
Append Data with a Header Mapping Table (14:54)
Pro 2️⃣ : Pro Function - List.Accumulate
List.Accumulate & What it Can Do (4:15)
List. Accumulate Logic Explained (11:06)
Rank by Number of Matches (List.Accumulate) (9:51)
Replace Multiple Words (List of Records) (11:50)
Replace Multiple Words (List of Lists) (4:51)
💪 Challenge with List.Accumulate - Append Data with Header Mapping Table (11:37)
🤔 QUIZ - Power Query Tips & Tricks
Lecture content locked
If you're already enrolled,
you'll need to login
.
Enroll in Course to Unlock