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
Lesson Content Locked
Your session expired. Please sign in again to continue.
Login
26 comments