Power Query for Excel Users : A Practical Guide to Cleaning, Transforming, and Automating Excel Data with Power Query.

Format:

Book

Author/Creator:

Valero, Oscar Martínez.

Language:

English

Subjects (All):

Data integration (Computer science).

Database management.

Physical Description:

1 online resource (424 pages)

Edition:

1st ed.

Place of Publication:

Birmingham : Packt Publishing, Limited, 2026.

Summary:

Learn Power Query in Excel to clean and automate business data: repeatable workflows for business users, with tips for using AI tools.No coding requiredFree with your book: DRM-free PDF version + access to Packt's next-gen Reader* Key Features Learn Power Query step by step, starting from everyday Excel tasks and building up gradually Organize.

Contents:

Intro

Power Query for Excel Users

A practical guide to cleaning, transforming, and automating Excel data with Power Query

Contributors

About the author

About the reviewers

Table of Contents

Preface

Who this book is for

What this book covers

To get the most out of this book

Download the example code files

Get the code bundle

Free benefits with your book

How to unlock

Share your thoughts

Introduction

Setting up your NOG-DataHub

Download and setup

Folder Structure

Before you begin

Part 1

Foundation and Mindset

1

Power Query Basics in Excel (ETL + UI Tour)

Technical requirements

What Power Query is (and what it isn't)

The ETL mindset: extract, transform, load

What Power Query is not

Where Power Query lives in Excel

Exercise: launching the Power Query Editor

Queries, steps, and refresh: the core loop

What is a query?

What are applied steps?

The refresh loop

Exercise: build your first query with steps

Load options: table vs. connection only

Testing refresh

Practice loads and your first trust loop

Why folders?

Exercise: load a CSV from a folder

Option A: from SharePoint folder (recommended)

Option B: from local folder

Filter to your file

Apply transformations

Load as Connection Only

Your first trust loop

Summary

2

Transform Daily Excel Tasks with Power Query

Why Power Query changes everyday Excel work

From Manual Cleanup to Repeatable Workflows

Recognizing tasks suited for Power Query

The NOG-DataHub: your data workspace

Why does structure matter?

Your first query: import, clean, refresh

Connecting to Amira's contacts file

Filter to the contacts file.

Cleaning up text

Fix the casing

Trim whitespace

Standardizing inconsistent values

Creating a DisplayName with column from examples

Setting data types and handling blanks

Loading and testing the refresh loop

Applied steps and the Power Query interface

Working with applied steps

Why step order matters

Passive M literacy: reading the formula bar

What each step actually does

Spotting steps that might cause trouble

Watch out for changed type

Multiple changed type steps

Hardcoded values in filters

Column references by name

Validate before you celebrate

Your quick validation checklist

Running the trust loop on Master_Contacts

Documenting your work

Add a query description

Rename your steps

Close, load and save

3

Connect and Standardize Common Business Sources

From scattered files to a single source of truth

Connecting to the sessions folder

Connect to the NOG-DataHub folder

Connect to a folder (SharePoint or local)

Filter to the sessions folder

Combine the files

Shaping raw Sessions into Master_Sessions

Check the headers

Remove extra columns

Set data types

Quick validation

Standardizing names, types, and labels

Discover the mess

Quick wins: session type, location, and status

The facilitator problem: why casing alone isn't enough

Load the mapping table

Connect to the folder

Filter to the file

Merge the mapping table into Master_Sessions

Why mapping tables beat scattered Replace Values

Protecting your reference tables

Check for duplicate keys

Run the duplicate check

Document your queries

Load as connection only

Final trust loop

4

Build Reliable Step Chains and Read M Passively

Why reliable step chains matter for everyday reports.

The other half of the solution

From messy weekly range to a structured query

Filter to the KPIs file

Remove gap columns

Remove blank rows and the TOTALS row

Fixing the WeekEnding type and errors

Handling mixed types

Bring in the session name

Load to a worksheet

Reading the applied steps and formula bar

Reopening the Power Query editor

Navigating the applied steps pane

Reading the formula bar

Protecting key columns and grain

Grain: what does one row represent?

SessionID: the key column

Documenting and debugging your query

Naming your steps

Writing a query description

A debugging workflow for when things break

Final validation checklist

5

Build Reusable Reports with PivotTables

Extending the query to include session types

Removing the worksheet table

Building stable PivotTables from Power Query output

PivotTable 1: registrations by week

PivotTable 2: attendance by session type

Using implicit measures correctly

Sum, Count, and Distinct Count

Checking and changing aggregation

Turning PivotTables into clear, stakeholder-friendly pages

Rename the value field header

Switch to tabular layout

Remove grand totals (if not needed)

Add clear titles

Reusing the pack: refresh, slicers, and safe handover

Adding a SessionType slicer

Connecting the slicer to both PivotTables

Adding refresh instructions

Preparing the workbook for a safe handoff

Part 2

Cleaning, Reshaping, and Trust

6

Combine and Clean Multiple Files Into One Clean Table

Why folder-based imports beat copy-paste

Combine Files: turning many exports into one table

Connect to the registrations folder.

Rename the query

Filter to the registration files

Review and confirm the columns

Cleaning the type, email, and status fields

Trim and clean text columns

Normalize email addresses to lowercase

Normalize casing across text fields

Fix remaining value variations

Simple duplicate rules that don't surprise you

Declare your dedup key

Sort and remove duplicates

The 30-Second trust loop after every refresh

Load the query to a sheet

The five checks

7

Combine and Reshape Multiple Files Into a Tidy Long Table

Why attendance needs its own table

Combine attendance files from a folder

Connect to the attendance folder

Rename the query

Filter to the attendance files

Keep only the columns you need

Cleaning keys for reliable matching

Standardize AttendeeEmail

Step 1 - trim

Step 2 - clean

Step 3 - lowercase

Standardize AttendanceStatus

Capitalize AttendeeName

Split-to-Rows: turning Multi-Session cells into rows

Spot the multi-value column

Split SessionIDs into rows

Verify the new grain

Keep SessionID as text

Landing on Sessions_Attendance and checking the grain

Rename and load the query

Validate with row count

Check 1: row count is higher than the raw file total

Check 2: no blanks in AttendeeEmail or SessionID

Check 3: AttendanceStatus contains only two values

8

Merge Tables to Build an Enriched Master List

What Master_Attendees is (and isn't)

Choosing merge keys that work

Why a single column is not enough

The composite key: AttendeeEmail + SessionID

Why the earlier cleaning steps are useful now

Building the master list with safe left joins

Understanding the join types.

Opening Registrations_Combined in the Power Query editor

Creating a new merged query

Selecting the merge keys

Renaming the new query

Expanding only the columns you need

Add an "Attended?" flag

Avoiding row explosion (duplicates and lookups)

What row explosion looks like

Check for duplicates with Group By

Fix the duplicates in Sessions_Attendance

The trust loop for merges (counts + spot checks)

Load Master_Attendees to a sheet

The four checks

Part 3

Combining and Automation

9

Join and Lookup Data Safely Without a Data Model

What Master_Payments is (and isn't)

Choosing keys and merge directions for payments

Why the composite key matters

Merge direction

Source grain

Join types at a glance

Using left, inner, and anti joins to find matches and gaps

Load Payments_Transactions

Rename the query and filter to the payments file

Open the file

Clean and standardize Payments_Transactions

Exercise A: left outer join - all registrations with payment details

Exercise B: Anti-joins - finding the gaps

Registrations_NoPayment

Payments_NoRegistration

Adding PaymentStatus to Master_Payments

Add the PaymentStatus column

Load Master_Payments to a table

Validating the join

Check 1: row count matches Registrations_Combined

Check 2: exception query row counts reviewed

Check 3: Spot-check known records

10

Automate Repetitive Processes

From one-off fixes to reusable patterns

Organizing your queries into groups

Creating the top-level groups

Populating the groups

Adding descriptions

Creating parameters for your workbook

Creating the SessionType parameter

Creating the FolderPath parameter

Building scoped views with parameter filters

Duplicating the master query.

Applying the parameter filter.

Notes:

Description based on publisher supplied metadata and other sources.

Part of the metadata in this record was created by AI, based on the text of the resource.

Other Format:

Print version: Valero, Oscar Martínez Power Query for Excel Users

ISBN:

9781807427726

OCLC:

1609715700