Mastering Microsoft Excel Power Query Editor
Course Code: MSEXPQE
Duration: 1 day
Price: £425
Upcoming Events
- Virtual Classroom
- Wed 8 July 2026
- 1 day
- £425.00 (€490.00)
- Virtual Classroom
- Mon 7 September 2026
- 1 day
- £425.00 (€490.00)
- Virtual Classroom
- Mon 14 December 2026
- 1 day
- £385.00 (€490.00)
- Virtual Classroom
- Tue 2 March 2027
- 1 day
- £385.00 (€490.00)
- Virtual Classroom
- Mon 21 June 2027
- 1 day
- £385.00 (€490.00)
Course Overview:
This course is designed to equip participants with the skills needed to effectively use the Power Query Editor in Microsoft Excel. Throughout the course, participants will learn to navigate the Power Query Editor interface, transform and clean data, and apply best practices in data preparation for analysis. The course includes handson modules covering topics such as importing data from multiple Excel Worksheets, Excel Workbooks and other various sources, using data transformation tools, and performing advanced data transformations like pivot tables and charts.
By the end of the course, participants will have a solid understanding of the tools available in the Power Query Editor and be able to confidently prepare data for analysis by using Pivot Tables, Charts and Power Pivot.
Target Audience:
Anyone who works with data analysis, needs to clean and transform large datasets from various sources regularly, and wants to efficiently prepare data for visualization and reporting within Microsoft Excel.
Course Objectives:
By actively participating in this course, you will learn about the following:
- Understand the purpose and benefits of using Power Query in Microsoft Excel.
- Navigate the Power Query Editor interface with confidence.
- Use the available tools to transform and clean data effectively.
- Apply best practices in data preparation for analysis.
- Using Pivot Tables from multiple Excel Worksheets, Workbooks, other data sources.
- Using the Power Pivot interface to create relationships and explore the function language (DAX) used.
Course Content:
What is Power Query
- What is Power Query and why use it?
- What can Power Query do
- Launching the Power Query Editor
Using the Power Query Editor
- Data sources Power Query connect to
- Methods of connecting to data
- Queries and connections
- Refresh control
Data Transformation Tools
- Removing and filtering rows
- Splitting and merging columns
- Changing data types
- Using the "Replace Values" feature
- Sorting and grouping data
- Adding custom columns
- Using "Conditional Column" for logical transformations
- Filling down or up for missing data
Advanced Data Transformations
- Pivoting and unpivoting columns
- Transposing data
- Using the "Merge Queries" and "Append Queries" tools
Analysing Data
- Using Pivot Tables and Charts to analyse multiple sourced data
- Create relationships for Pivot Tables
- Use Power Pivots for advanced data modelling features
- Use Data Analysis Expressions (DAX) to solve a number of basic data modelling and analytical problems.
Best Practices and Final Q&A
- Naming conventions for queries and applied steps
- Organising and documenting queries
Prerequisites:
We recommend that delegates have existing experience with Excel or Databases and should understand basic data concepts, with some experience in Pivot Tables and Pivot Charts. No prior experience with Power Query is required.
Software Requirements for Virtual Classroom
If you are attending one of our Virtual Classroom courses, you will need to have Microsoft Excel 2013, 2016, 2019 or 365 (desktop version) installed on your device. You may notice differences in software if using a MAC. For the best experience we also recommend that you have two screens and a working microphone. A webcam is optional, but very much appreciated by our trainers!
Certification / Exam:
There is no exam relating to this course.
Further Information:
For more information or to book this course, please contact our Course Enquiries Team on 0117 370 1970 or email us at enquiries@skilltec.co.uk
