Master Excel Power Query - Beginner to Pro (including M) By Leila Gharani For Immediate Digital Download!

Master Excel Power Query - Beginner to Pro (including M)
Collect, Combine and Analyze Data with Ease

How This Single
Button
Can Change Your Life

Make Your Excel Life Easier with Power Query

Inside your Microsoft Excel window sits a magic button.

Those who have pressed this button are familiar with the relief it brings.

If you’ve never clicked this button, keep reading, because this will be the most important message of your day.

This magic button? It’s called the “Refresh” button, and it looks like this:

You’re probably asking…

“Okay Leila, you’re making a big deal about this button. What makes it so special?"

Because, my dear, just one click of this button can replace hours and hours of manual labor.

It can save you from pulling your hair out as you clean data. And it allows you to ship reports to your boss and colleagues faster – winning you praise and promotions.

We’ll revisit our magic button shortly. First, let’s visit a typical day in the life of an Excel user.

“Here’s Your Data Dump. Enjoy!”

Ugh.

Is there anything that spikes your blood pressure more than being sent a massive data file you’ll need hours to clean?

You reply to your colleague through gritted teeth:

“Thanks! Will have you those reports asap!”

You stare at the file the same way you stare at that closet you haven’t cleaned in seven years.

“I’m afraid to even touch it.”

Yet, you must. So, you open the file…

“My gosh, it’s even worse than I thought.”

You’re staring at a crime scene.

“I’ll try copy & pasting…”

Is copying and pasting your method of choice? It makes sense.

After all, a quick Ctrl-c and Ctrl-v and voilà! Everything is perfect, right?

In our dreams.

Copying and pasting is fraught with problems:

• It’s frustrating, especially if you’re dealing with multiple sheets and workbooks
• It’s too easy to make a mistake and leave data behind (Repeat after me: “No data left behind!”)
• Each time you receive new data you must repeat the copy/paste over and over and over and over…
• What if the data is in a text file which has more data rows than Excel can handle?

Get my point?

But let’s say you’re able to get the data into Excel.
Off to the races?

Hardly!

It’s one thing to have data in Excel, it’s another to have clean data in Excel.

Oh, how we wish all data could be delivered to us on a silver platter. We’d give our colleague a warm, “Thank you!” and maybe even a hug.

Instead, we groan…

“You put the data in a PDF? Really?!”

Or…

“Why the heck would you put the customer ID and the customer’s name in the same freaking column!?”

Or…

“Why does your SAP export have leading spaces?"

But you push your frustration down deep, check how many days are left til’ Friday, and soldier on…

“Let me add a helper column”

You get to work cleaning and shaping the data. It’s like ironing a crumpled suit you found in the back of that long-neglected closet.

You need a tool.

“I’ll add a helper column,” you say to yourself. “That will help me remove these dang spaces."

You add a helper column. Then another, and another.

Next, you start adding complex formulas. You’re hoping they will do the heavy lifting and iron out your messy data.

They do, but at a cost.

After lunch, you sit back down at your desk and re-launch your Excel file.

“Uh-oh.”

Excel freezes for a split-second, and then finally, your file opens.

“It’s slowing down.”

All those lengthy formulas and helper columns bloated your file.

“If I send my boss this 40MB file, she’ll kill me.”

You look down at your coffee mug and decide it needs refilling.

Your Excel life doesn’t need to be this way!

If you regularly import data into Excel, please, let me make your life easier.

I have two words for you: Power Query.

Often overlooked because Microsoft tucked it away inside the “Data” tab back in 2016, Excel users worldwide are finally waking up to the gift that is Power Query.

Because you’re not alone in your struggles to add and clean data in Excel. Not by a long shot.

Before graduating from our Power Query online course, our students felt the same way.

"Many times we receive our data in less than pristine condition. This course is filled with tools and techniques that will allow us to solve these problems with much less effort. Tasks that we would not undertake due to complexity and effort are now enjoyably achievable.

- Stephen Azzarello

"“I underwrite value on mobile home parks and RV’s.

99% of the time, the books and records come to me as PDF documents. If it wasn't for Power Query, I would spend more time converting than actually doing my core job of underwriting.”

- James W.

"I was still going through the course when my wife, who is a consultant, received an assignment from a client to help them recover the data about their P&L after virus compromised their data.

She had to reconcile the current data with some older extracts and the files were huge and not easy to reconcile with normal look-ups due to multiple instances of the same account.

Her colleagues tried to do the job for some time and failed so they asked her to try to solve the issue. I managed to help her using Power Query with some of the techniques from this course, I finished the task in a couple of hours (since I am not yet familiar with PQ and it took me some time to troubleshoot some issues, otherwise would have been minutes). And my file ended up only at 1.5 MB, very fast to open and work with. The previous attempt was about 45 MB, full of formulae, heavy to open and work with and also it caused occasional Excel crashes... I can't believe how easy this was really!"

- Dragan Yordanov

Today, these students and thousands more are saving hours each day thanks to Power Query.

I swear, using Power Query must be the same feeling people had 100 years ago when they drove a car for the first time. It lets you go fast.

In that spirit, it’s time for you to trade in your horse & buggy and enter the Excel fast lane.

Data to Dashboard in 60 Seconds

Need to send a dashboard to your boss or colleague?

Ah, we finally return to that magic refresh button. Because do you want to know the best part of Power Query?

• It’s not that it can connect to almost any data source, even data sitting on a webpage.
• It’s not Power Query’s ability to clean data with a flick of its wrist.
• It’s not how Power Query eliminates complex formulas, liberating you from having to become Severus Snape, Excel potions master.

Yes, those are all wonderful benefits provided by Power Query. But its best feature?

It’s this: Power Query remembers.

Yes, you can record the steps you take to import and clean your data. That way, when a new batch of data arrives, all you need to do is click “Refresh.”

Instantly, your data is imported, cleaned, and beautifully presented.

No more copy & paste. No more helper columns. No more mixing together complex formulas.

You simply click that magic button. That’s how you go from data to dashboard in 60 seconds.

This has saved me quite some time which I used to spend doing things manually.
I have prepared some other reports by combining data from multiple files in a folder. Any time a new file was added to the folder, I would see the latest numbers in my report.

I helped a colleague with a CAPEX report using PQ, and she told me that I saved her one whole day of work.

In the words of José Carlos (a student of ours) - "Can we call that magic :)?"

How many hours are you losing
by not fully harnessing Power Query?

If you regularly import data into Excel, it’s a no-brainer. Your time is too precious to not use Power Query.

But in my experience, even most daily Power Query users are only scratching the surface of what’s possible.

For example, they don’t realize Power Query allows them to:

• Connect directly to websites and fetch data
• Remove duplicates in an intelligent way
• Hunt down errors inside columns (for example, when different date formats are used in the same column – don’t you love that?)
• Combine data from multiple Excel workbooks into a single Table (or Pivot Table)
• Consolidate data from all files in a folder (and make exceptions as you need)
• Connect your Excel workbook to Microsoft Outlook

Not to mention the file-shrinking wonders Power Query provides. Look at this experiment I ran…

Without using Power Query, the size of this Excel file was a whopping 41MB. After Power Query?

The file size shrank to 5MB!

Would you rather send a 41MB or a 5MB file to your boss?

I Avoided Power Query For a Long Time

If you’re new around here, I’m Leila Gharani, founder of XelPlus.

I avoided Power Query for a long time. I had watched multiple videos and I thought,

“This is a Power Tool. I don’t work with millions of rows of data. I don’t need this.”

But then in 2018, the Microsoft Team presented some updates they’d implemented in Power Query. And I realized, I had watched the wrong videos. There was an easy side to Power Query and an advanced side. I’d jumped right in with advanced techniques. No wonder I tried to avoid it.

You see, as a Microsoft MVP, I get invited to Redmond, Washington each year for the Global MVP Summit. In between the yearly visits, we have monthly sessions where the Microsoft Excel team gives us updates on the latest features (yes, it’s pretty cool).

It was on one of these calls where I saw the easy side of Power Query in action for the first time. My initial reaction was, “Wow! That is incredible.”

I also realized my mistake. You see, at first, the name Power Query fooled me.

Judging by the name, I thought it was a tool for power users. When actually, it’s a power tool – for everyone.

Power Query is not reserved for Excel masters. Rather, it’s a powerful tool that can benefit even the Excel beginner. At the end of the day, Power Query is nothing more than a series of clicks.

After that meeting with the Microsoft team, I knew I must teach Power Query to our XelPlus audience. That’s when I started working on our online course.

Today, I’m excited to invite you to become a student in that course. It’s now helped thousands of Excel users shave hours off their workday tasks, and it can help you too.

Introducing: Master Excel Power Query

A step-by-step online course by Leila Gharani to help you leverage Excel’s biggest time-savings feature

Course Curriculum

Section 1: Introduction

• Applications of Power Query
• ❗Important: Excel Versions, Expectations & Course Pathways
• (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
• What is a Proper Data Set?
• (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?
• Power Query Overview: Import Large Data from Another File 
• Power Query Editor & Basic Transformation
• Quick Insights on Data Quality & Distribution (Hidden Feature)
• Formula Bar, Applied Steps & M Code
• Close & Load Destinations (How to Change Them) 
• Refresh Data & PQ Refresh Options 
• Import CSV File & Extract Text Based on Pattern
• Merge Data with Another File (Pivot Table from Multiple Files)
• Old School Method: What if There Was NO Power Query?
• Activity: Get Familiar with PQ

Section 4: Important Power Query Tips & Tricks

• Feature Name Change in Office 365 in 2021
• Uploading Data From Excel 
• The Hidden Table Method (Hidden Feature) 
• Handling Changes to Source
• Data Types 
• Data Types vs. Formatting & Null Values
• Power Query Navigation Shortcuts (Become Pro) 
• Finding & Correcting Errors in Data 
• More Data Views: Duplicate OR Reference Query?
• Keeping an Eye on Query Dependencies
• Delete, Manage, Copy Queries & Backup Results (Become Pro) 
• 💪 CHALLENGE - Find and Correct the Mistake
• 🤔 QUIZ - Power Query Tips & Tricks
• Best Practice for Power Query 

Section 5: Helpful Power Query Transformations

• Text Transformations (Format, Extract & more)
• Merging Columns & What to Watch Out For
• Fill & Replace Values to Create Proper Datasets
• Sort Data including Multiple Levels
• Remove Duplicates including Multiple Columns
• Number Transformations & What to Watch Out For
• Working with Filter (AND & OR Conditions)
• 💪 CHALLENGE - Can you Find the Mistake? Problem with Filter (Become Pro)
• Change Type & Remove Columns Trap (Become Pro)
• 📝 Key Takeaways - Helpful Power Query Transformations

Section 6: Powerful Power Query Transformations

• Column From Examples - Extract Patterns Quickly
• Allocate Data to Groups or Buckets (Become Pro)
• Conditional Columns in Power Query
• Aggregating (Grouping) Data on Multiple Levels
• Group By for All Rows (Become Pro)
• Unpivot Columns - Basics
• Unpivot & How to Overcome Common Errors (Become Pro)
• Pivot Columns - Basics
• Problem with Split by Delimiter (that's easy to miss)
• Split Column by Rows instead of Columns (Become Pro)
• 💪 CHALLENGE - Summarize Sales By State
• Solution: Summarize Sales By State (Bonus Map Chart Included) 

Section 7: Date & Time Transformations

• Date Transformations (Extract Age, Weekday etc.) 
• Creating Dates from Text or Columns (Become Pro) 
• Time Transformations (Calculating Hours worked) 
• Date & Number Errors When Importing Data (Locale)
• 💪 CHALLENGE - Pivot Table for Hours Worked by Month & Week Number
• Solution: Pivot Table for Hours Worked by Month & Week Number 
• 🤔 QUIZ - Date & Time Transformations

Section 8: Custom Column & Basic M Manipulation

• Important Basic Power Query M Logic
• Why Use Custom Columns
• Introduction to "Add Custom Column"
• Custom Columns Type Compatibility & Intrinsic Functions
• Skipping Steps in Power Query (Become Pro)
• Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
• Drill-Down in Power Query
• Custom Formulas for Template Creation (Hidden Tip)
• 🤔 QUIZ - Custom Columns and Basic M
• 📝 Key Takeaways - M Basics

Section 9: Power Query Online Data Sources (Online)

• Connecting to different Sources 
• Import Data from a Website 
• Automatically Connect to Files on Websites (Hidden Feature)
• Import Data from PDF (multiple & bulk import)
• Import Data from ODATA
• Get Google Sheet Data with Power Query
• Connect to Outlook Online (Microsoft Exchange) 
• Connect to SharePoint or OneDrive for Business 
• Pro Tip: How to Change Source from Local to SharePoint 
• 💪 CHALLENGE - Google Sheets Survey "Your Dinner Plans" 
• 🎁 BONUS FREE LIBRARY & NEW Custom Data Types 

Section 10: Combining / Appending Data

• Why Append Data? The Difference Between Merge & Append 
• Combine / Append Data from Multiple Workbooks 
• Combine All Files in a Folder (with Excel Tables) 
• Combine All Files in a Folder (Without Excel Tables) 
• How to Adjust Folder Path from Local to SharePoint Drive 
• Combine All Sheets in a File (Pivot Table from Multiple Sheets) 
• Overcome Potential Errors when Combining Sheets (Become Pro) 
• Consolidate Data from Multiple Sheets in the Current Workbook 
• 💪 CHALLENGE - Consolidate Data from Folder (with a twist)
• Solution: Consolidate Data from Folder (with a twist) 
• 📝 Tips & Key Takeaways When Appending in Power Query

Section 11: PROJECT - Report on HR Data

• Test Your PQ Knowledge by Completing This Project 
• Calculate Years Worked in Each Position 
• Calculate Years Worked in the Company
• Split First, Last & Middle Names to Separate Columns 
• 📝 Key Takeaways - Data Analysis & Preparation with Power Query

Section 12: Merge Options - Join Kind Options Explained

• Overview of Merge Options and Join Kinds 
• Left Outer Join & Right Outer Join 
• Merge Based on Multiple Columns (Become Pro) 
• Can You Find the Mistake? Merging Text Columns 
• Merge Data to Get Multiple Match Results & Bonus Tip 
• Inner & Full Join in Power Query 
• Left & Right Anti Join when Merging in Power Query 
• How to Use Fuzzy Match in Power Query 
• Fuzzy Match with Transformation Table (Hidden Feature) 
• 💪 CHALLENGE - Someone Hacked the Salary File - Find Mismatches in Data
• Solution: Someone Hacked the Salary File - Find Mismatches in Data 
• 📝 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 
• Availability of Power Pivot
• Pivot Table from Multiple Excel Tables 
• Power Pivot Table with Data Model & Power Query 
• Create a Calendar Table in Power Pivot 
• Pivot Slicers & TimeLine with Power Pivot & Power Query (Become Pro) 
• 📝 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 
• 🛣️ 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) 
• Search and Replace Bulk Values 
• Calculate Value Difference to Previous Row 
• Approximate Match Lookup with Merge 
• 💪 CHALLENGE: Create a Report based on Multiple Parameters
• Solution: Create a Report based on Multiple Parameters 

Section 16: Advanced Problem Solving using the Power Query Interface

• Learning Objectives for this Section
• Assign Unique Number to Group 
• Advanced Unpivot Techniques 
• Advanced Pivot Techniques 
• Incremental Data Load & Self Referencing Query (Hidden Tip)
• 📝 Key Takeaways: Advanced Problem Solving

Section 17: PROJECT: Create a Modern Excel Dashboard with Power Query & Pivot Tables

• Excel Dashboard Project Overview 
• Prepare a Draft of the Dashboard 
• Import Master data from External Workbook with Power Query 
• Import Data from Text File with Power Query 
• Create the Data Model & Define Relationships in Power Pivot 
• Create Logic for Latest and Previous Month in Power Query 
• Setup Calculations with Pivot Tables for Latest Month 
• Link Excel Shapes to Data & Linked Picture Trick 
• Top 3 Sales Managers & Numbers (Sorted Excel Pivot Table) 
• Linked Table for Sales by Product Category 
• Excel Pivot Chart for Monthly Sales 
• Pivot Slicer Connected to Multiple Pivot Tables 
• Finalize the Excel Dashboard 
• 📝 Key Takeaways: Excel Dashboard Project

Section 18: Understanding M Formula Language (Let, Lists, Records & Improving Performance)

• M Language - How M Thinks (let Expression & Values) 
• Defining & Invoking Custom M Functions 
• Reference Guide for Standard M Functions 
• Lists & Records and how to Reference a Specific Cell in a Table 
• Summarizing Brackets & Lookup Operators in M code 
• Creating Lists and Tables inside Power Query 
• Understand Each Keyword & the Purpose of _ (underscore) 
• Using Power Query Parameters 
• IF Then & Lookup Operators to Lookup Values in Previous Row 
• Error Handling - Bulk Replace Lookup with Try Otherwise 
• Speed up Queries: Table.Buffer & How to Test Impact (Hidden Tip) 
• Query Folding - Improve Performance for Relational Databases 
• 🤔 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 
• Power Query Text Functions (Text.Contains, Text.Replace)
• How to Use List Functions in Custom Column
• Merge Values from Rows into One Cell 

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
• Better Collaboration: File Path from a Cell (Formula.Firewall Error)
• Import from Folder: Mixed Letter Case Headers & Trailing Spaces
• Import All Sheets BUT Data Starts from Different Rows
• 💪 CHALLENGE: Combine Sheets & Get Name from Cell Value
• Solution: Combine Sheets & Get Name from Cell Value 

Section 22: PROJECT - Power Query and Power BI

• Project Overview of Power BI Dashboard
• Install Power BI Desktop & Quick Overview
• Upload & Transform Data with Power Query in Power BI
• Setup Data Model & Create Relationships in Power BI
• Create Your Dashboard & Reports within Minutes
• Publish Dashboard to Power BI Service 
• 📝 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
• My Favorite Resources

Master Excel Power Query - Beginner to Pro (including M) By Leila Gharani, What Is It Included (Content Proof: Watch Here!)