Power Query in Microsoft Excel
Power Query in Microsoft Excel is a course in pulling data from files, folders, databases and PDFs into Excel with Power Query, then cleaning, reshaping and combining it ready for analysis, with M language basics and automatic refresh. It suits people who combine the same reports every month and those who prepare data for PivotTables or Power BI.
- Duration12 hours · 2 days
- FormatOnsite / live online
- Next roundOn request
- CertificateIncluded
Course overview
Preparing data in Excel often takes longer than the analysis itself: copying sales files from several branches together, trimming spaces and fixing dates column by column, or turning a report exported from another system into a proper table, and then doing it all again every month. Power Query, on Excel's Data tab, records every action as an applied step, so when new data arrives you simply select Refresh and the same work is repeated correctly.
This hands-on course starts with importing data from Excel and CSV files, whole folders, databases, web pages and PDFs, cleaning and reshaping it with Unpivot, Pivot, Fill and Group By, and combining tables with Append and Merge. It then moves on to M language basics, parameters, custom functions and error handling, and closes with feeding the results into PivotTables, the Data Model and Power BI, and a workshop building a monthly reporting system that refreshes with one click.
What you’ll gain
- Import data from many sources into Excel with Power Query and choose the right load option
- Clean and reshape data for analysis while recording the steps so they can be repeated
- Combine, split and match tables with Append, Merge, Unpivot and Group By
- Read and edit basic M code, and create parameters and custom functions
- Build a monthly file consolidation that refreshes automatically and copes with errors
- Feed Power Query results into PivotTables, the Data Model and Power BI
Who this course is for
- People who combine and tidy data in Excel every day, week or month
- People who prepare data for analysis in PivotTables, Power Pivot or Power BI
- Accounting, finance, sales and purchasing staff who receive report files from several systems
- Data analysts and report writers who want to cut out manual copying and fixing
Prerequisites
- Able to use basic Excel formulas such as SUM, IF and VLOOKUP or XLOOKUP
- Able to handle basic data tasks such as sorting, filtering and creating a simple PivotTable
- No programming or M language experience is needed
- A Windows laptop with Excel from Microsoft 365 or Excel 2024
Curriculum
Course Details
This course is the 2026 edition of Power Query in Microsoft Excel, rewritten for Excel in Microsoft 365 and Excel 2024. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs, at Intermediate level. Day one practises importing data from many sources, cleaning, reshaping and combining tables. Day two covers the M language, parameters, custom functions, monthly file consolidation with automatic refresh, and feeding data into the Data Model, PivotTables and Power BI. Learners take home practice and answer files for every lab and the reporting system built in the workshop.
Day 1 Importing, Cleaning and Reshaping Data
Section 1: Lab: Getting to Know Power Query in Excel
- What Power Query is, and the idea of extract, transform and load (ETL)
- The Power Query Editor, the Queries pane, the formula bar and applied steps
- Loading results as a table, a PivotTable, a connection only or to the Data Model
- How Power Query differs in Excel for Windows, Mac and Excel for the web
- Lab: a first query from an Excel table, refreshed when the data changes
Section 2: Lab: Importing Data from Many Sources
- Get Data from Excel files, Table/Range and Text/CSV, choosing the right encoding for Thai
- From Folder and Combine Files to stack every file in a folder into one table
- Data from databases such as SQL Server or Access, and from web pages
- Tables from PDF files, and what to watch for when the document layout changes
- Lab: combine every branch's sales file from a single folder
Section 3: Lab: Cleaning Data
- Check data quality with column quality, column distribution and column profile
- Change data types and locale so dates and numbers are read correctly
- Remove blank, duplicate and error rows, with Trim, Clean and Replace Values
- Split Column, Extract and Column from Examples
- Lab: clean up a report exported from another system ready for analysis
Section 4: Lab: Reshaping Data with Unpivot, Pivot and Group By
- Unpivot Other Columns to turn a wide table into a long one
- Pivot Column and Transpose when you need a wide result
- Fill Down, Use First Row as Headers and handling reports with stacked headings
- Group By for totals, counts and averages by group
- Lab: turn a wide monthly sales report into a table ready for PivotTables
Section 5: Lab: Combining Tables with Append and Merge
- Append Queries to stack tables with the same structure
- Merge Queries and the Left, Inner, Full and Anti join kinds
- Finding records that do not match between two tables with an anti join
- Fuzzy matching when names are not spelled exactly the same
- Lab: match sales to product data and find items missing from the product list
Day 2 The M Language, Automation and Next Steps
Section 6: Lab: Custom Columns and M Language Basics
- Conditional columns and custom columns with calculation operators
- The let and in structure, the Advanced Editor and referring to columns with each
- Common Text, Number, Date and List functions
- if then else conditions and data types in M
- Lab: calculate discounts, receivable ageing and quarters with custom columns
Section 7: Lab: Parameters, Custom Functions and Errors
- Parameters for folder paths and values that change often
- Create a custom function from a query and call it with Invoke Custom Function
- Handle errors with try and otherwise, and use Keep Errors to inspect them
- Group queries and name steps so others can maintain them
- Lab: a reusable function that cleans customer names in several tables
Section 8: Lab: Monthly File Consolidation with Automatic Refresh
- Understanding the helper queries and sample file created by Combine Files
- Filtering out unrelated files, and coping when a new file adds or renames columns
- Refresh All, refresh on open and Query Dependencies
- Query speed: basic query folding and Disable Load
- Lab: import a new month's sales by dropping the file into the folder and refreshing
Section 9: Lab: On to PivotTables, the Data Model and Power BI
- Load several tables into the Data Model and create relationships
- PivotTables and PivotCharts from several tables without VLOOKUP
- Power Query in Excel and Power BI share the same M language, and moving queries between them
- Sharing files through OneDrive or SharePoint, and access to data sources
- Lab: a sales report by product category and branch from three tables
Section 10: Workshop: An Automated Reporting System
- Workshop: design the steps to combine data from several sources for a real task
- Clean, reshape and match the data in a single set of queries
- Summarise with a PivotTable and test the refresh with a new month's data
- A checklist before handing the file over to the team to use and maintain
Schedule & training options
For individuals — public rounds
No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770
For organisations — in-house / private
- Tailor the content to your team’s tools and projects
- Your dates, at your office or live online
- Quotation with tax ID for procurement
Instructors
Frequently asked questions
What is Power Query and what is it used for in Excel?
Power Query is the Get & Transform tool on Excel's Data tab. It pulls data from many sources, such as Excel and CSV files, whole folders, databases, websites and PDFs, then cleans and reshapes it ready for analysis. Every action is recorded as an applied step in the M language, so when new data arrives you simply select Refresh and all the steps run again, which removes repetitive monthly preparation.
How do you combine several Excel files in one folder with Power Query?
Keep files with the same column layout in one folder. Go to Data, Get Data, From File, From Folder, choose the folder, select Combine & Transform Data and pick the sample sheet or table. Power Query stacks every file into one table with a column for the source file name. Tidy the data in the Power Query Editor and select Close & Load. Next month, drop the new file into the folder and refresh. Filter out unrelated files before combining so the refresh does not fail.
What does Unpivot do in Power Query and when should you use it?
Unpivot turns a wide table into a long one. A table with separate columns for January, February and March becomes two columns, month and sales. Select the columns to keep, such as product code, right-click and choose Unpivot Other Columns. Long data is easier to use in PivotTables, filters and charts, and new months are handled without changing the steps. Pivot Column does the reverse.
Do I need to know the M language before learning Power Query?
No. Most Power Query work is done through menus and buttons, and the M code is written for you. This course starts with the menus, then shows you how to read the code in the formula bar and Advanced Editor, write custom columns with each and if then else, create parameters and custom functions, and handle errors with try otherwise, so you can adjust more complex queries yourself.
Who is the Power Query in Microsoft Excel course for, and what should I know beforehand?
It suits people who repeatedly combine, split and reshape data in Excel, and those who prepare data for PivotTables, Power Pivot or Power BI. You should already use Excel formulas such as VLOOKUP or XLOOKUP and handle basic tasks like sorting, filtering and PivotTables, using Excel from Microsoft 365 or Excel 2024 on Windows.
Can the Power Query in Microsoft Excel course be run in-house for our team?
Yes. It can be run in-house for your team, with examples adapted to your own work, such as practising on report files in the same format your team combines every month. You can request a quotation from this page, or contact us on LINE @itgenius or by phone on 02-570-8449.
Related articles
Articles are published in Thai.