Join my FREE 1 Hour Excel Masterclass. Move From Competent To Exceptional: Reclaim 5+ Hours Every Week

Home
Training Options
All Training Options EXCEL'erator PROGRAM Corporate Training Solutions Shop - On Demand Training Videos Customised Live Group Training
Shop Testimonials
Free Training
EXCEL Masterclass Pre-recorded Training (Free)
All Microsoft 365 training

Live training with Eithne Dolan

Microsoft Excel Power Query

Refresh instead of repeat

I’ll help you spend less time preparing data manually and more time using it. Build a Power Query process once, then refresh it when new data arrives.

View Course OutlineDownload PDF

Course outline: PDF, 2 pages

Group size
Recommended maximum: 10
For an interactive session
Delivery
Live virtual or in person
Demonstrations, guided practice, discussion and supplied exercise files
Duration
6 hours
One 6-hour session or two 3-hour sessions

Is this course for you?

Does any of this sound familiar?

If you regularly work with data in Excel, you may spend more time getting it ready than actually analysing it: copying files together, fixing dates, splitting columns and filling blanks. Then doing it all again next month. Do you...

  • Regularly copy and paste data from one spreadsheet to another?
  • Receive weekly or monthly files that need the same cleaning steps each time?
  • Spend time removing unwanted rows, columns, blanks or duplicates?
  • Constantly need to fix dates, numbers or badly formatted text?
  • Combine data from multiple worksheets or workbooks?
  • Receive a separate file every month and manually add it to a master spreadsheet?
  • Use XLOOKUP or VLOOKUP to bring information from one table into another?
  • Work with reports that look fine but are badly structured for PivotTables or analysis?
  • Repeat the same steps every week or month before you can start reporting?
  • Worry that copying, pasting and manual changes could introduce errors?
  • Have heard of Power Query but aren’t sure what it does or where to start?

If any of the above resonates, this course is for you.

What you’ll be able to do

  • Connect to Excel Tables, workbooks, CSV and text files, with an overview of folder, OneDrive and SharePoint connections.
  • Build repeatable cleaning steps to remove unwanted data, fix text and apply correct data types.
  • Reshape cross-tab reports into analysis-ready tables using Fill, Pivot and Unpivot.
  • Append tables, merge related information and identify matched and unmatched records.
  • Combine monthly files from a folder and refresh when new files arrive.
  • Add Custom and Conditional Columns, date calculations and grouped summaries.
  • Understand Applied Steps and get an introduction to M code without needing to become a programmer.
  • Load prepared data to Excel, PivotTables or the Data Model and troubleshoot refresh problems.

Your course outline

Fourteen modules. Repeatable results.

Connect to data, clean and reshape it, combine information from multiple sources, and create repeatable processes for your Excel reports and dashboards.

01What is Power Query?
  • Power Query and the Extract, Transform and Load process
  • When to use Power Query rather than formulas or manual copy and paste
  • The Power Query workflow: connect, transform, combine, load and refresh
  • Access Power Query in Excel
  • Understand queries, source data and output data
02Getting Data
  • Load data from an Excel Table/Range
  • Connect to Excel workbooks and CSV/text files
  • Overview of folder, SharePoint and OneDrive connections
  • Navigate the Power Query Editor
  • Preview data and understand column profiling tools
03Managing Rows & Columns
  • Use first row as headers
  • Choose, remove and reorder columns
  • Filter rows
  • Remove blank rows, duplicates and errors
  • Keep or remove top and bottom rows
  • Sort data appropriately
04Text & Number Transformations
  • Trim and Clean text
  • Change case
  • Split and merge columns
  • Extract text
  • Replace values
  • Apply number operations and rounding
05Data types, nulls and errors
  • Apply correct text, number, date and time data types
  • Recognise the effect of incorrect data types
  • Manage null and blank values
  • Find, investigate and correct errors
  • Use locale-aware data types where relevant
06Reshape data for analysis
  • Fill Down and Fill Up
  • Transpose data where appropriate
  • Pivot columns
  • Unpivot columns and Unpivot Other Columns
  • Convert cross-tab reports into analysis-ready tables
07Summarise & Calculate
  • Group By using Sum, Count, Average, Minimum and Maximum
  • Group using one or more columns
  • Create Custom Columns
  • Create Conditional Columns
  • Use date transformations to derive year, quarter, month, week and age
  • Introduction to formulas created in Power Query
08Append Queries
  • Append two or more tables
  • Append Queries compared with Append Queries as New
  • Understand how Power Query aligns columns by heading
  • Identify problems caused by inconsistent source structures
09Merge Queries
  • Join two related tables using matching fields
  • Prepare matching columns and data types
  • Understand Left Outer, Inner and Anti joins
  • Expand selected fields from the merged table
  • Identify matched and unmatched records
10Combine Files in a folder
  • Import and combine files from a folder
  • Understand the sample file and transformation query
  • Apply one set of transformations to every file
  • Add a new monthly file and Refresh
11Applied Steps & M-code
  • Understand how Power Query records each transformation
  • Use, rename, reorder, edit and delete Applied Steps
  • Use the Formula Bar
  • Introduction to M code and the Advanced Editor
  • Use meaningful step and query names
12Query Management
  • Duplicate a query
  • Reference a query
  • Understand the difference between Duplicate and Reference
  • Organise queries into groups
  • View query dependencies
  • Enable or disable loading where appropriate
13Sources, settings and troubleshooting
  • Change a file or folder source
  • Respond to renamed or moved files
  • Understand Data Source Settings, credentials and permissions
  • Introduction to privacy levels
  • Diagnose refresh and source-structure errors
14Close & Load options
  • Load to an Excel table
  • Create Connection Only queries
  • Load to the Data Model where appropriate
  • Load prepared data to a PivotTable
  • Refresh queries and connected PivotTables

What you’ll learn to do

You’ll get practical experience across realistic business tasks, learning how to turn raw data into clean, combined, report-ready information.

Connect and clean

  • Excel Tables, workbooks and CSV files
  • Remove blanks, duplicates and unwanted rows
  • Fix text, dates and data types
  • Create cleaning steps you can reuse

Reshape and calculate

  • Fill Down, Pivot and Unpivot
  • Turn cross-tabs into usable tables
  • Custom and Conditional Columns
  • Grouped totals and date calculations

Combine your data

  • Append tables to add rows
  • Merge tables using matching fields
  • Left Outer, Inner and Anti joins
  • Identify matched and unmatched records

Refresh recurring reports

  • Combine monthly files from a folder
  • Apply the same steps to every file
  • Manage queries and Applied Steps
  • Load, refresh and troubleshoot

Build it once. Refresh next month.

Instead of opening, copying, pasting, cleaning and reformatting every new monthly file, you’ll build one process that applies the same transformations each time. Next month: add the new file and refresh. You’ll leave with practical techniques you can apply immediately to your own Excel work.

Before you join: a good working knowledge of Excel. Familiarity with Excel Tables and basic PivotTables is recommended. No previous Power Query experience is required.

Let’s make Excel work for you

Book a free 20-minute consultation with me to discuss your team, your Excel experience and your training goals.

Book a Free Consultation