Mastering Microsoft Excel Power Query Editor [SKT_MSEXPQE]

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.

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.

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.

CONTENT

Introduction to Power Query Editor

• What is Power Query and why use it?

• How Power Query fits into Microsoft Excel’s ecosystem.

• Importing data from various sources (Multiple Excel Worksheets, Multiple Excel Workbooks, CSV, databases, and other sources).

• Launching the Power Query Editor.

Navigating the Power Query Editor

• Overview of the Power Query Editor interface.

• Understanding applied steps and query dependencies.

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.

• Creating and editing parameters.

• Working with the "Invoke Custom Function" feature.

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.