Leila Gharani – Automate with Power Query: Recipes to Solve Business Data Challenges
BECOME MORE CONFIDENT & CREATE RELIABLE SOLUTIONS
You’ve gone down the Power Query rabbit hole and finally setup a query that worked… only to see it fail a few weeks later.
No more wasting time coming up with solutions.
This course gives you the practice you need to become more confident so you can solve any new data challenges in a fraction of the time it used to take you.
You’ll also discover new automation options so you can automate an entire work flow – not just a part of it.
What you’ll achieve
- Solution recipes you can apply to your own work
- Learn automation techniques to solve problems with less effort
- Discover new automation tools like Power Automate & Office Scripts
- Make your working life easier and more enjoyable
The Secret Sauce for Data Analysis
Does This Sound Familiar?
- You’ve started using Power Query at work, but you don’t feel confident enough.
- You know the end result you want, but don’t know what the best approach is to get there. After all, each problem is different.
- Maybe you have PDF files that go over multiple pages, and you just can’t find the right approach to bringing over the information properly into Excel.
- Maybe you need to Unpivot or Pivot – but you’re not really sure how to use these properly.
- Maybe you have a complex Lookup problem and you’re not sure if you need Power Query or formulas?
- You’ve noticed other automation tools like Power Automate and Office Scripts and wondered if you can add these to your workflow. Maybe trigger a notification when a file becomes ready or split a file and create PDF versions, or take automatic backups of your files (couldn’t that come in handy?).
If you’ve experienced any of this then you are in the right place.
By going through this program, you’ll expand your toolkit so you can solve new data challenges in a fraction of the time it used to take you. Plus you’ll discover new automation options you hadn’t thought of before.
What You’ll Learn In Automate with Power Query
Introduction
- Welcome & How to Get the Most Out of This Course
- 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
Module 1: PDF to Excel – Correctly Extract PDF Data
- Combine Multiple PDF Pages into One Table (Dynamic vs Static Approach)
- Discover Powerful Features to Clean Messy PDF Data (Table of Contents)
- Find and Extract Tables From PDFs No Matter Where They Are
- Bulk Import Multiple PDF Files From Folder (The Method)
- Bulk Combine PDFs and Extract Values From Different Locations
- 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
- Create a Pivot Table From Multiple Tables in the Current File
- 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)
- Professional Pivot Tables (Formatting Tips to Take Your Pivot Tables to Another Level)
- Split Pivot Tables to Multiple Categories (ONE Click Solution)
Module 3: Organizing and Managing Power Query Files
- File & Folder Organization – Best Practice Guide
- Important Refresh Settings in Excel (Automatic Refresh)
- Add Last Refreshed Date & Time to Your Report
- Copy Power Queries from one File to Another
- 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)
- SharePoint / OneDrive Password Changed? Easily Re-authenticate
- Connect to Files on Dropbox (The Proper Way)
- SharePoint Folder Connection Made Easy with These Tricks
- Latest File as Source – Name Doesn’t Matter
Module 5: Real World Data Transformation Challenges Solved
- Power of UnPivot & Pivot – Learn the Basics for Success
- Multi-Column Lookup (Amazon Barcodes) – UnPivot vs Formula Solution
- Data Reconciliation – How to Compare Two Tables and Find Differences
- Get the Latest File and Unpivot a Monthly Cash Flow Statement
- Convert Multi-Line, Single Column File into Table (Pivot Data)
- Keeping Leading Zeros Extracts from SAP, Oracle etc.
Module 6: Data Quality Management Challenges
- Adding Comments to Results & Keeping Rows in the Right Order (Self-Referencing Query)
- How to Audit and Locate Excel Formula Errors with Power Query
- 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
- Case Sensitivity & How to Ignore Letter Case
- Filter Pitfall & How to Avoid Missing Data
- Removing, Reordering, Renaming Columns Pitfall & How to Ignore Missing Headers
- Changed Type Pitfall & Automatic Type Detection – On or Off?
- Other Common Power Query Errors & How to Fix These
Module 8: Managing Source Automations
- Get a Teams Message when a New Source File is Added to Folder (Power Automate)
- 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)
- Schedule Backup of Files with Power Automate
Module 10: Automations to Format & Distribute Files
- Recording Actions with Office Scripts (OS)
- Looping & Formatting Sheets with Office Scripts (OS)
- Running Office Scripts with Power Automate (Schedule or Event Trigger)
- Auto Convert Files to PDF (PA)
- Automatically Save Each Sheet as a Separate File (VBA)
- Automatically Send Emails With Attachments to People Based on Condition (PA)
Course Wrap-up
- Favorite Power Query Pro Tips
- Final Words
Sale Page: Leila Gharani – Automate with Power Query: Recipes to Solve Business Data Challenges