Search our courses

Course Duration:

2 days


You will be need to be working with Excel 2016 Professional Plus or Office 365 ProPlus


The course will show you how to:-

  • create simple, elegant data models in PowerPivot;
  • use these data models to create pivot tables based on multiple tables;
  • use Power Query to build queries to extract, transform and load data;
  • start to use the DAX language for more in-depth analysis of data;
  • analyse time series data (“compare this month’s sales with the same period in the previous quarter”);
  • create and display KPIs in Excel;
  • create Power View reports within Excel based on your data models; and
  • create and show Power Maps (including making videos of trends over time).

Subject Areas:

Preparing for Power BI

  • Version of Excel needed
  • Installing add-ins
  • Basic multi-table pivot tables

PowerPivot data models

  • Importing SQL Server tables
  • Filtering rows/columns
  • Data and diagram view
  • Hiding from client view
  • Relationships

Pivot tables using PowerPivot

  • Drill-down vs Quick Explore
  • Slicers and timelines
  • Controlling what you see

Using Excel tables

  • Creating and naming tables
  • Adding to data models

Using other data sources

  • Importing Access and Excel
  • Importing cubes
  • Pasting from the clipboard

Power Query sources

  • Querying databases
  • Web pages and XML files

Transforming data in Power Query

  • Splitting columns
  • Merging and inserting columns
  • Other transforms

Calculated columns

  • Creating calculated columns
  • Using the RELATED function

Calculated fields (measures)

  • The all-important query context
  • Implicit calculated fields
  • AutoSum fields
  • Creating in Excel and PowerPivot

DAX basics

  • The DAX Studio add-in
  • Calculating ratios
  • Aggregate X functions

The CALCULATE function

  • Replacing filters
  • Using ALL
  • Using VALUES to edit filters

More advanced DAX functions (*)

  • Using FILTER to filter tables
  • Ranking
  • The EARLIER function


  • Creating in Excel or SQL
  • Linking to calendars
  • Sorting months
  • Multiple date tables

If you would like to book onto a course or find out more information please fill out our contact form or call us on 01454 203 355.