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

When will I receive my course?

You will receive a link to download your course immediately or within 1 to 21 days. It depends on the product you buy, so please read the short description of the product carefully before making a purchase.

How is my course delivered?

We share courses through Google Drive, so once your order is complete, you'll receive an invitation to view the course in your email.

To avoid any delay in delivery, please provide a Google mail and enter your email address correctly in the Checkout Page.

In case you submit a wrong email address, please contact us to resend the course to the correct email.

How do I check status of my order?

Please log in to MMOCourse account then go to Order Page. You will find all your orders includes number, date, status and total price.

If the status is Processing: Your course is being uploaded. Please be patient and wait for us to complete your order. If your order has multiple courses and one of them has not been updated with the download link, the status of the order is also Processing.

If the status is Completed: Your course is ready for immediate download. Click "VIEW" to view details and download the course.

Where can I find my course?

Once your order is complete, a link to download the course will automatically be sent to your email.

You can also get the download link by logging into your mmocourse.hk account then going to Downloads Page.