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 hands-on 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.
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.
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.
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 required.
Software Requirements for Virtual Classroom
If you are attending our Virtual Classroom training, you will need a laptop or PC with a working microphone and two screens. A webcam is also recommended for the best experience!
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.