banner

Leila Gharani – Master Excel Power Query – Beginner to Pro (including M)

img

Leila Gharani – Master Excel Power Query – Beginner to Pro (including M)

Leila Gharani - Master Excel Power Query - Beginner to Pro (including M)

BECOME THE EXCEL HERO WITH POWER QUERY

Discover the smarter, faster way to handle data with our Power Query course. You’ll be able to turn every data task into a chance to stand out. Become the go-to Excel problem-solver at work with this essential skill.

This course is for everyone, no matter what job you do. We’ll start from scratch and before you know it, you’ll become a Power Query expert.

Think of this course as your all-in-one solution. While you might find snippets of Power Query knowledge on YouTube, they fall short. Ours is comprehensive, covering niche, real-world topics that free content usually doesn’t touch. It’s about making your work life not only easier but also more enjoyable.

What you’ll achieve with our Power Query Course

Effortless Efficiency!

Yes, efficiency can be effortless once you discover a new way of handling your data. The only regret? Not discovering this sooner – which is what we hear the most from our members. But remember the saying: The best time to get started was yesterday. The second best time is today.

Become the Office Star

Even if you prefer to stay low-key, your newfound Power Query skills will speak volumes. You’ll naturally become known as the go-to problem-solver in your office, a reliable expert whom colleagues admire and turn to for solutions.

Master Problem-Solving Techniques

Start using outside-the-box techniques to tackle Excel challenges. Whether it’s combining data from various sources, importing from PDFs or the web, or tidying up messy data filled with spaces, blank cells or messed up columns, this course will teach you smarter techniques, saving you time and energy.

How Power Query Can Change Your Life

Make Your Excel Life Easier with this Power Query Training

Inside your Microsoft Excel window sits a magic button.

It’s not called “Power Query” – instead it’s called “Get Data” (in the Data tab).

Your data could be ANYWHERE. It could be sitting right there on the sheet you’re on. It could be in another Excel file, in a text file, on the web or in a PDF document.

The instant you click this button, a new world opens up, helping you to quickly complete tasks – whether it’s cleaning up mixed-up rows or messy columns, combining data with other files or anything else you need done.

Once you know your way a round – even just a little bit – you get to do things that only Excel gurus used to be able to do.

Now these advanced techniques are just a “click” away for you.

They can save you from pulling your hair out as you clean data.

You can replace hours and hours of manual labor.

And you can ship reports to your boss and colleagues faster – winning you praise and promotions.

The best part: Power Query remembers your steps!

Next time you need to do the same thing, you just open your file and click “Refresh” – then sit back and enjoy the show (i.e. watch the magic happen as your data updates automatically).

Let me give you more details – let’s visit a typical day in the life of an Excel user.

 

Start using Excel in the Power Mode. Just by finishing the FIRST hour, you’ll be amazed at what you can achieve with Power Query. It will give you tons of ideas on how to make your work easier.

Here’s Your Learning Journey

Leila Gharani introduces you step-by-step to Excel’s biggest time-savings feature.

You’ll be up and running with Power Query in just an hour. You can view the detailed curriculum below.

See anything that jumps out? Which lesson do you think you’d begin with?

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

Section 2: 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

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

Change Type & Remove Columns Trap

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

Pivot Columns – Basics

Problem with Split by Delimiter (that’s easy to miss)

Split Column by Rows instead of Columns

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 Data Sources (Online)

Connecting to different Sources

Import Data from a Website

Automatically Connect to Files on Websites (Hidden Feature)

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: All 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

Section 14: 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)

Rank by Group (Nested Rank) with Power Query

Running totals by Month (Invoking functions)

How Far You’ve Come!

Section 21: Flexible Importing of Data with the Help of M 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

What You’ve Achieved!

 

It doesn’t matter if you’re a beginner, either..

  • You don’t need to be an advanced Excel user
  • You don’t need to understand “M Logic”
  • You don’t need to be working with BIG data
  • You don’t need to invest hours of learning Power Query before reaping the benefits

In fact, I give you this challenge…

Go through just a couple modules. Pick whichever ones jump out at you. Then, test your new skills at work. Give it an honest effort.

I promise your eyes will open to all the hours you’ve been wasting.

If you’re not impressed? We have a 14-day refund policy and will gladly return your enrollment fee.

But I don’t think that will happen, because once you get the hang of Power Query, you’ll never look back. Nobody does.

There are no reviews yet.

Add a Review
Original price was: $199.00.Current price is: $59.00.

Discover the smarter, faster way to handle data with our Power Query course. File Size: 6.11 GB.

Purchase this product now and earn 59 Points!
10 Points = $1