Duration: 120 Minutes
This course will introduce participants to Power Query, a powerful data transformation tool in Microsoft Excel and Power BI. Participants will learn how to import, clean, and transform data efficiently, enabling them to work with large datasets and perform data analysis with ease.
Course Requirements
Excel 2016 or later or Power BI Desktop installed. Basic familiarity with Excel (e.g., knowing how to navigate worksheets and basic formulas)
What is Power Query
Overview of Power Query's capabilities
Use cases in Excel and Power BI
Key benefits for data cleaning and transformation
Navigating the Power Query Interface
Where to access Power Query (Excel/Power BI)
Overview of the Power Query Editor window
Key components: Queries, Ribbon, Applied Steps, and Query Settings
Importing Data
Connecting to various data sources (Excel, CSV, Web, Database)
Loading data into Power Query
Basic data preview and filtering during import
Overview of the Query Pane
Understanding queries and the query structure
Overview of the Applied Steps section
Cleaning and Filtering Data
Removing duplicates, blank rows, and errors
Using the Filter pane for sorting and filtering
Working with Data Type
Changing column data types (Text, Date, Number)
Auto-detecting data types
Text & Column Transformations
Splitting columns, merging columns, trimming and cleaning text
Pivoting and Unpivoting Data
Pivoting data to summarize by category
Unpivoting columns into rows for better analysis
Using Group By and Aggregations
Grouping data by categories
Applying summary functions (Count, Sum, Average)
Saving and Loading Data
Loading data into Excel or Power BI
