Power Query: import, clean and refresh data
For analysts, coordinators, finance and operations staff who rebuild the same report from exports every week or month and want the cleaning to repeat itself. Prerequisite: 'Spreadsheet data cleaning: spaces, text numbers, dates and duplicates', or equivalent experience cleaning data with worksheet formulas. You'll import CSV files, workbook tables and whole folders into Power Query with the right connector settings, build and maintain cleaning steps (headers, data types with the right locale, replace, fill down, unpivot, duplicates), fix step-level and cell-level errors when a source changes, combine queries with Append and Merge under suitable privacy levels, and load, refresh and verify the output. It uses Power Query in Excel for Windows (Data > Get Data); Excel for Mac and the web have Power Query with some differences. Based on Microsoft Learn's Power Query documentation and Microsoft Support's Excel pages. Your organisation's approved data sources and data classification rules apply.
- Level
- Advanced
- Length
- About 130 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Import a CSV file, a workbook table or a folder of files into Power Query with the right connector settings
- Build cleaning steps in the Applied Steps list: remove top rows, promote headers, set data types with the right locale, replace values, fill down and unpivot
- Identify and fix step-level and cell-level errors after a data source changes
- Combine queries with Append and Merge, choosing the join kind and privacy levels that fit the data
- Load and refresh a query, then verify row counts and totals before the output is used
Course outline
- 1.Importing a CSV file, workbook table or folder with the right connector settingsLesson · 22 min
- 2.Building cleaning steps in the Applied Steps listLesson · 26 min
- 3.Identifying and fixing step-level and cell-level errorsLesson · 22 min
- 4.Combining queries with Append and Merge, join kinds and privacy levelsLesson · 22 min
- 5.Loading, refreshing and verifying the outputLesson · 15 min
- 6.Power Query: import, clean and refresh data: knowledge checkKnowledge check · 14 questions
- 7.Power Query: import, clean and refresh data: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Learn: What is Power Query?
- Microsoft Support: About Power Query in Excel
- Microsoft Learn: Text/CSV connector (delimiters, file origin, data type detection)
- Microsoft Support: Import data from a folder with multiple files (Power Query)
- Microsoft Learn: Combine files overview
- Microsoft Learn: Using the Applied Steps list
- Microsoft Learn: Data types in Power Query (type detection and locale)
- Microsoft Learn: Promote or demote column headers
- Microsoft Learn: Fill values in a column
- Microsoft Learn: Replace values and errors
- Microsoft Learn: Unpivot columns
- Microsoft Learn: Working with duplicate values
- Microsoft Learn: Using the data profiling tools
- Microsoft Learn: Dealing with errors in Power Query
- Microsoft Learn: Append queries
And 5 more.
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.