Autoplay
Autocomplete
Previous Lesson
Complete and Continue
Automate Excel: Power Query & Power Automate Recipes
Introduction
Welcome & How to Get the Most Out of This Course (3:50)
⚙️ How to Use the Platform for Best Learning Experience
🔍 Looking for a Specific Topic? Use the Transporter!
❗Important Power Query Editor Settings & Other Useful Info
👆 One-Click Method to Change Source for Multiple Queries (11:19)
Module 1: PDF to Excel - Correctly Extract PDF Data
Combine Multiple PDF Pages into One Table (Dynamic vs Static Approach) (10:16)
Importing PDF: Multi-Page Tables with Floating Rows Between Pages (11:31)
Discover Powerful Features to Clean Messy PDF Data (Table of Contents) (11:51)
Find and Extract Tables From PDFs No Matter Where They Are (8:55)
Bulk Import Multiple PDF Files From Folder (The Method)
Bulk Combine PDFs and Extract Values From Different Locations (Contracts) (12:20)
Bulk Combine PDF Payslips & Create a Dashboard (Messy PDFs)
Module 2: Create Pivot Table from Multiple Sources
Import Sheets Between Two Tabs: 3-D Reference Power Query (6:31)
Create a Pivot Table From Multiple Tables in the Current File (6:29)
Appending Vs Merging Vs Relationships - When to Use Which When Combining Data
How to Use Results of Pivot Tables in Reports (GETPIVOTDATA vs Direct Reference) (8:38)
Professional Pivot Tables (Formatting Tips to Take Your Pivot Tables to Another Level)
Split Pivot Tables to Multiple Categories (ONE Click Solution) (7:29)
🆕 Enjoying the Course so far? Refer a friend and get 30% off your next XelPlus purchase!
Module 3: Organizing and Managing Power Query Files
File & Folder Organization - Best Practice Guide
Important Refresh Settings in Excel (Automatic Refresh) (8:27)
Add Last Refreshed Date & Time to Your Report (2:35)
Copy Power Queries from one File to Another (3:08)
Best Practice for Naming Steps When Working in Different Languages
Module 4: Managing Source - Online Files (SharePoint, OneDrive, Dropbox)
Connect to Local SharePoint Files the Right Way (Avoid Changing Source) (4:18)
SharePoint / OneDrive Password Changed? Easily Re-authenticate
Connect to Files on Dropbox (The Proper Way) (2:48)
📂 SharePoint Folder Connection Made Easy with These Tricks
🕒 Latest File as Source - Name Doesn't Matter (6:48)
Module 5: Real-World Data Transformation Challenges Solved
Module Overview: UnPivot, Pivot and Other Practical Data Transformations for the Real World (2:17)
Multi-Column Lookup (Amazon Barcodes) - UnPivot vs Formula Solution (9:35)
Data Reconciliation - How to Compare Two Tables and Find Differences (13:57)
Get the Latest File and Unpivot a Monthly Cash Flow Statement (4:19)
Convert Multi-Line, Single Column File into Table (Pivot Data) (5:58)
Convert Single Column with Headers on Top of Values into Table (8:10)
How to Align Data from Different Sources: Tips for Accountants and Analysts (13:16)
Consolidate Data from Excel Forms (HR Onboarding Example) (12:00)
Complex Tag Mapping (CAD / BOM Extracts) (17:55)
Keeping Leading Zeros Extracts from SAP, Oracle etc.
Add Placeholders for Missing Data (Assign Data Types & Names in Advance) (9:53)
Dealing with Random Line Breaks in Text Files
Module 6: Data Quality Management Challenges
Adding Comments to Results & Keeping Rows in the Right Order (Self-Referencing Query) (8:16)
How to Audit and Locate Excel Formula Errors with Power Query (4:54)
Creating Master Data from One Large Fact Table with Power Query
Module 7: Common Pitfalls to Avoid
When Power Query Goes Wrong - Pitfalls, Bugs, Errors and What's Causing Them
Combine Tables, Expand Columns Pitfall & How Not to Hardcode Headers (12:02)
Case Sensitivity & How to Ignore Letter Case (8:10)
Filter Pitfall & How to Avoid Missing Data
Removing, Reordering, Renaming Columns Pitfall & How to Ignore Missing Headers (6:59)
Changed Type Pitfall & Automatic Type Detection - On or Off?
Dynamic Changed Type & Renaming Based on Column Position (8:57)
Ready-to-use Functions for Dynamic Data Types & Column Renames (10:08)
Function to Fix Inconsistent Thousand/Decimal Separators
Find Problem Files When Combining From Folder (2 Cases)
Other Common Power Query Errors & How to Fix These
**** POWER QUERY & BEYOND: INTRODUCING OTHER AUTOMATION OPTIONS ****
🤖 Excel Automation Options
Module 8: Managing Source Automations
Get a Teams Message when a New Source File is Added to Folder (Power Automate) (5:22)
Automatically Copy Files from One SharePoint Folder to Another (Power Automate)
Automatically UnZip Files on SharePoint (Power Automate)
Module 9: Backups Automation
Automatically Create Query-Free Backups (VBA) (5:47)
Schedule Backup of Files with Power Automate
Module 10: Automations to Format & Distribute Files
Recording Actions with Office Scripts (OS) (7:14)
Looping & Formatting Sheets with Office Scripts (OS) (7:53)
Running Office Scripts with Power Automate (Schedule or Event Trigger) (6:05)
Auto Convert Files to PDF (PA) (7:35)
Automatically Save Each Sheet as a Separate File (VBA)
Automatically Send Emails With Attachments to People Based on Condition (PA) (9:06)
🎁 BONUS - QuickFix Automation Recipe Book 📖
🔻DOWNLOAD: QuickFix Automation Recipe Book
Course Wrap-up
My 10 Favorite Power Query Pro Tips (9:49)
🙌 YOU DID IT! (2:18)
✍️Leave a Review
My Favorite Resources
🏅 Share Your Digital Certificate (0:40)
Welcome & How to Get the Most Out of This Course
Complete and Continue
Discussion
26
comments
Load more
26 comments