MS Excel – Data Analysis with Power Query

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.

There is no final exam. Active participation during the training is sufficient for successful completion.

• 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.