Data Analysis with Power Pivot for Excel

PowerPivot is an add-in for Microsoft Excel that allows users to work with millions of rows of data across different data sources. In view of the speed and ability to manage data between heterogeneous data. PowerPivot is becoming an indispensable tool for data mining in the era of big data.

The PowerPivot course will teach users how to construct their data models using various data sources (e.g. Excel tables , Access tables or SQL tables), and performing analysis with the Data Analysis Expression ( DAX ) functions and Pivottables.

The easy to follow exercises and small class size teaching approach can ensure the best learning result. It is an ideal course for people working in the accounting, financial analysis and data processing areas. Students can choose the daytime, evening and weekends classes to suit their learning needs.  

What you’ll learn

Understanding Power Pivot

  • What is PowerPivot
  • Why use PowerPivot
  • Limitation of Classic Excel PivotTables
  • PivotTables in PowerPivot

Different Data Sources

  • Overview of external source types, e.g., relational databases and text files
  • Importing Access Tables
  • Linking or adding Excel Tables
  • Loading data from SQL server

What is a data model?

  • Normalization
  • Knowing different kinds of joins ( 1 to many, many-to-many etc)
  • Using Power Query to import data

Creating data models

  • Creating connections to data sources
  • Editing table properties
  • Defining data Tables
  • Knowing data types
  • Creating relationships (auto detect and manual)
  • Diagram View vs. Data View
  • Defining hierarchies

PPI4091 - 廣東話 27 Sep enrol
PPI4111 - 廣東話 04 Nov enrol
  Excel Dashboards and Reports
  Excel VBA X ChatGPT/GPT
  Excel VBA - Advanced
  Excel-formulas & functions

Introduction to Data Analysis Expression Language (DAX)

  • Calculation in DAX
  • DAX Data Types and DAX Operators
  • Evaluation context
  • Calculated columns and measures
  • Working with DAX functions
  • Time Intelligence Functions
  • Information functions
  • Logical and text functions

Data Analysis with PivotTables

  • Knowing different kinds of PivotTables
  • Classical and Flattened PivotTables
  • PivotTables on PowerPivot
  • Defining the PivotTable
  • Working with Report Filter
  • Custom sorts
  • Computing Ratios and percentages
  • Data aggregation
  • Year-to-date calculations
  • Add Visualizations to a PivotTable
  • Conditional formatting and Sparklines
  • PowerPivot Slicers vs. Excel Slicers
  • Filtering Data using Slicers