Why take this course
Are you tired of manually copying, filtering, and processing data in Excel? With Power Query, you will learn modern data automation techniques that save time and enable you to create dynamic, interactive reports.
This training demonstrates how to leverage the Business Intelligence capabilities available in Microsoft 365—without writing code. You will learn how to efficiently collect, transform, analyze, and prepare data for reporting using Power Query and Excel.
What you'll learn
- Import and combine data from multiple sources using Power Query.
- Automate data cleansing, transformation, and analysis processes.
- Create queries with conditions and parameters.
- Dynamically group and aggregate data.
- Prepare data for reporting and presentation.
- Build PivotTables, PivotCharts, and management dashboards.
Certification & Exam
Upon completion of the training, participants receive a personalized certificate confirming their skills in data analysis using Power Query and Microsoft Excel.
There is no final exam. Active participation during the training is sufficient for successful completion.
Who is this course for
Topics covered
Introduction to Power Query
- Self-service Business Intelligence tools:
- Power Query
- Power BI
- Power Query as an ETL (Extract, Transform, Load) tool.
- Understanding the architecture and capabilities of the Power Query add-in.
Importing Data
- Introduction to data import capabilities and key Power Query components.
- Importing data from text files:
- *.txt
- *.csv
- Splitting data using delimiters.
- Importing data from Excel workbooks:
- Named ranges
- Worksheets
- Tables
- Importing data from multiple *.xls files located in local or network folders.
Combining Data (Queries)
- Combining multiple source tables for consolidated analysis using PivotTables.
- Introduction to data merging.
- Understanding different types of relationships and dependencies between datasets.
Merging Data (Queries)
- Left Outer Join (equivalent to VLOOKUP/XLOOKUP scenarios).
- Merging data using aggregation functions.
- Merge types:
- Left Outer Join
- Inner Join
Grouping Data
- Introduction to data grouping.
- Grouping data and creating value summaries using:
- Count Rows
- Grouping data using:
- Sum functions
- Ranking fields
Data Preparation and Transformation
- Introduction to pivot and transformation operations.
- Using transformation functions:
- Transpose
- Unpivot
- Pivot Column
- Working with different types of input data requiring transformation.
Creating Additional Columns
- Creating calculated columns using:
- Addition
- Subtraction
- Row Count
- Date functions
- Using Power Query transformation tools:
- Merge
- Round
- Extract
- Replace
- Fill
- Split Columns
Logical Operations and Conditional Columns
- Using conditional columns.
- Creating calculated columns using:
- IF functions
Parameters and Query Parameterization
- Creating parameters.
- Filtering queries using parameter values.
PivotTables
- Working with data from Excel and external data sources.
- Building PivotTables.
- Using:
- Slicers
- Timelines
- Data models:
- Data relationships
- Data consolidation
- Splitting PivotTables using report filter pages.
- Practical analysis scenarios using PivotTables.
- Creating PivotCharts.
- Building management dashboards.
- Connecting PivotTables through slicers for interactive reporting.
