Duration
2 Days (5 hours per day)
Level
Basic Level
Design and Tailor this course
As per your team needs
Edit Content
This comprehensive course will equip you with the skills to leverage Power Query, Excel’s powerful data transformation tool. You’ll learn to import, clean, shape, and combine data from various sources, automating repetitive tasks and building robust data models for analysis and reporting. Through hands-on exercises, you’ll gain practical experience working with real-world scenarios.
Edit Content
This course is ideal for:
- Business analysts
- Data analysts
- Financial Analysts
- Anyone who works with data in Excel and wants to improve efficiency and accuracy
Edit Content
- Understanding the role of Power Query in data transformation (ETL)
- When to use Power Query vs. formulas in Excel
- Benefits of Power Query (data cleaning, automation, etc.)
- Hands-on: Explore the Power Query interface and navigate the Power Query Editor
- Data source options and connection settings
- Data preview and understanding data structure
- Transforming data using the Power Query interface (filtering, removing duplicates, formatting)
- Changing data types (text to number, dates, etc.)
- Hands-on: Import data from various sources (Excel files, CSV, text files, web)
- Identifying and handling common data quality issues
- Filtering, sorting, and removing unwanted data
- Filling missing values and handling nulls
- Splitting, merging, and transforming columns
- Text manipulation functions (uppercase, lowercase, extracting specific characters)
- Hands-on: Clean data by removing errors, handling missing values, and formatting inconsistencies
- Introduction to Apache Spark for Fabric
- Different methods for combining data sets (merge, append, union)
- Matching and joining tables based on common fields
- Grouping data and creating summary tables
- Aggregating data using functions (sum, average, count)
- Hands-on: Merge and append data from multiple sources
- Introduction to M Language (optional) – for more complex transformations
- Using parameters and queries as functions
- Error handling and troubleshooting common Power Query issues
- Best practices for building efficient and maintainable Power Query workflows
- Hands-on: Create custom columns using formulas (M Language – optional)
- Refreshing Power Query data and keeping reports up-to-date
- Integrating Power Query with PivotTables and Charts
- Building interactive dashboards with slicers and filters
- Hands-on: Apply Power Query transformations to build reports and dashboards in Excel
- Introduction to Power BI (brief overview)
- Leveraging Power Query in Power BI Desktop
- Sharing and collaborating on data models (optional)
Edit Content
- Basic understanding of Excel
- Familiarity with data manipulation concepts (sorting, filtering, etc.)
