FA-0747Data & Analytics

Excel Power Query for Analytics

Repeatable data preparation, M expressions and workbook dashboards

Introduction

Why this course

Use Excel Power Query to build repeatable preparation and consolidation workflows for tabular data. Progress from importing files and inspecting query steps to transformations, custom M expressions, multi-file joins and a workbook dashboard.

Practice uses supplied or appropriately licensed sample datasets and assessments within the major modules. Advanced patterns and external connectors are demonstrated selectively according to the capabilities and access available in the training environment.

Learning outcomes

Import, clean and transform sample Excel, CSV/text and permitted online data.
Reshape ERP-style exports into analysis-ready tables.
Consolidate workbook, sheet and folder sources using appropriate append/merge operations.
Use selected custom M expressions and functions for repeatable preparation.
Choose worksheet versus Data Model load destinations.
Create relationships and PivotTable/PivotChart outputs in a compatible Power Pivot environment.
Assemble an interactive Excel dashboard with appropriate slicers and date controls.

Prerequisites

Comfortable using Excel with:

xlsx
- csv
- txt
Column based formula creation
Pivoting
Filing system
Character manipulation
Joins and types
Data types

A compatible desktop Excel edition with the Power Query, Data Model and Power Pivot features needed by the labs.
Access to supplied files and authorised accounts for optional web, SharePoint/OneDrive or Exchange exercises.

Training outline

8 modules

01Module 1 — Query Basics (approximately 2.5 hours)6 topics

Import sample data and inspect size/quality rather than assuming all large files process quickly.
Power Query Editor, basic transformations, data-quality/distribution views where available.
Formula bar, Applied Steps and M code.
Close/load destinations, refresh and refresh options.
CSV text-pattern extraction and merging with another file for a PivotTable example.
Compare a conventional Excel preparation method; assessment.

02Module 2 — Query Management (approximately 1.5 hours)5 topics

Current Excel Get & Transform interface and workbook-source import.
Named/table source references and a supplied hidden-table pattern where appropriate.
Handle source changes, data types versus formatting, nulls and errors.
Navigation, data views and query dependencies.
Delete, manage and copy queries; preserve output snapshots; assessment.

03Module 3 — Transformations (approximately 3 hours)14 topics

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)
ASSESSMENT
Challenges with Filter
Change Type & Remove Columns Trap
Column From Examples - Extract Patterns Quickly
Allocate Data to Groups or Buckets
Conditional Columns in Power Query
Aggregating (Grouping) Data on Multiple Levels

Group By with All Rows; unpivot as a separate operation.

Unpivot & How to Overcome Common Errors
Pivot Columns - Basics
Problem with Split by Delimiter
Split Column by Rows instead of Columns
ASSESSMENT

04Module 4 — Dates and M Expressions (approximately 2 hours)9 topics

Date Transformations (Extract Age, Weekday etc.)
Creating Dates from Text or Columns
Time Transformations (Calculating Hours worked)
Date & Number Errors When Importing Data
ASSESSMENT
Important Basic Power Query M Logic
Logic behind Custom Columns
Introduction to "Add Custom Column"
Custom Columns Type Compatibility & Intrinsic Functions

Inspect step dependencies before bypassing a transformation.

Adjusting FILTER & Conditional Columns to Reference a Dynamic Variable
Drill-Down in Power Query
Custom Formulas for Template Creation
ASSESSMENT

Introduce a small reusable custom M function for a selected task.

05Module 5 — External Sources (approximately 0.5 hour)6 topics

Compare connector availability, authentication and refresh requirements.
Introduce permitted web tables/files and suitable database/service sources.
Use an authorised Google Sheets export/import example in Excel; the dedicated Power Query Google Sheets connector is documented for Power BI/Fabric, not Excel.
Introduce Exchange Online mailbox import where available and authorised.
SharePoint/OneDrive for Business sources and changing local paths to service paths.
Short source-selection assessment.

06Module 6 — Combining and Joining Data (approximately 2.5 hours)12 topics

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
Consolidate Data from Multiple Sheets in the Current Workbook
Overview of Merge Options and Join Kinds
Left Outer Join & Right Outer Join
Merge Based on Multiple Columns
Merging Text Columns

Expand multiple matching rows and inspect changed row counts.

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
ASSESSMENT

07Module 7 — Data Model and Power Pivot (approximately 0.5 hour)7 topics

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
ASSESSMENT

08Module 8 — Analytics Patterns and Dashboard (approximately 2 hours)8 topics

Messy Data from Multiple Rows to One Row
Search and Replace Bulk Values
Calculate Value Difference to Previous Row
Approximate Match Lookup with Merge
ASSESSMENT
Assign Unique Number to Group
Advanced Unpivot Techniques
Advanced Pivot Techniques

Review a supplied incremental/self-referencing workbook pattern, its state/dependency risks and refresh limitations; do not equate it with Power BI managed incremental refresh.

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
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
ASSESSMENT

A programme built around your team.

Share your training goals and requirements.