Microsoft Office training -  PowerPivot / Excel Power BI

Excel Training

PowerPivot / Excel Power BI (2 days)

£650 + VAT for most venues (£750 + VAT for London)

Excel can do so much more than simple pivot tables!  This two-day 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).

Note that you'll need to be using Excel 2013 Professional Plus or Office 365 ProPlus to be able to use the Power BI (Power Business Intelligence) features within Excel.

Course details

  • Locations for PowerPivot / Excel Power BI coursesLocations:Manchester, London
  • Next PowerPivot / Excel Power BI course locationNext course:Tue 17 January 2017
  • PowerPivot / Excel Power BI course timesTime:09.30-16.30

Course Contents

Preparing for Power BI

  • Training topicVersion of Excel needed
  • Training topicInstalling add-ins
  • Training topicBasic multi-table pivot tables

PowerPivot data models

  • Training topicImporting SQL Server tables
  • Training topicFiltering rows/columns
  • Training topicData and diagram view
  • Training topicHiding from client view
  • Training topicRelationships

Pivot tables using PowerPivot

  • Training topicDrill-down vs Quick Explore
  • Training topicSlicers and timelines
  • Training topicControlling what you see

Using Excel tables

  • Training topicCreating and naming tables
  • Training topicAdding to data models

Using other data sources

  • Training topicImporting Access and Excel
  • Training topicImporting cubes
  • Training topicPasting from the clipboard

Power Query sources

  • Training topicQuerying databases
  • Training topicWeb pages and XML files

Transforming data in Power Query

  • Training topicSplitting columns
  • Training topicMerging and inserting columns
  • Training topicOther transforms

Calculated columns

  • Training topicCreating calculated columns
  • Training topicUsing the RELATED function
  • Training topicBLANK and SWITCH

Calculated fields (measures)

  • Training topicThe all-important query context
  • Training topicImplicit calculated fields
  • Training topicAutoSum fields
  • Training topicCreating in Excel and PowerPivot

DAX basics

  • Training topicThe DAX Studio add-in
  • Training topicCalculating ratios
  • Training topicAggregate X functions

The CALCULATE function

  • Training topicReplacing filters
  • Training topicUsing ALL
  • Training topicUsing VALUES to edit filters

Covered if time allows If time

More advanced DAX functions

  • Training topicUsing FILTER to filter tables
  • Training topicRanking
  • Training topicThe EARLIER function


  • Training topicCreating in Excel or SQL
  • Training topicLinking to calendars
  • Training topicSorting months
  • Training topicMultiple date tables

Date functions

  • Training topicPeriod to date
  • Training topicFiscal year calculations
  • Training topicParallel periods
  • Training topicSemi-additive measures


  • Training topicCreating hierarchies
  • Training topicPros and cons


  • Training topicRelative KPIs
  • Training topicAbsolute KPIs

Power View basics

  • Training topicThe data model again
  • Training topicCreating visualisations

Power View Filters

  • Training topicSlicers
  • Training topicUsing tiles
  • Training topicFiltering data

Other visualisations

  • Training topicCreating matrices and cards
  • Training topicChart multiples
  • Training topicChart drill-down
  • Training topicScatter and bubble charts

Power View maps

  • Training topicAssembling your data
  • Training topicCreating maps
  • Training topicDrill-down in maps

Covered if time allows If time

Power Map (GeoFlow)

  • Training topicCreating maps
  • Training topicScenes, tours and layers
  • Training topicCreating videos
  • Training topicReasons not to use

Why Choose Us?

View full list of benefits View full list of benefits

Sample customer feedback

"Enjoyed the pace of the course - trainer and training materials were excellent."

Ben Candy (The WKCIC Group (prev City and Islington College))

"Good. Very well prepared training course."

Nathanael Pinder (Close Asset Management Ltd.)

"All really good thanks. Had good reviews on Google, so booked."

Michael Barlow (Garic Ltd)

"Course delivered in a very easy to understand manner, good content and manuals."

Siobhan McNulty (Hewlett Packard)

"Showed structure and importance of data model, with lots of practical examples, answered specific requests of most attendees and best lunches I've ever had on a course."

Rainer Pratl (CFA Institute)

There are lots more where these came from (or just refresh this page to change the quotes shown).

This course uses Excel 2013 Professional Plus or Office 365 ProPlus (the first version of Excel to integrate PowerPivot, Power View and Power Map within the application, although Power Query is still a separate add-in). 

Related Courses

Power BI Desktop

Power BI Desktop and Web

2 day course

SSAS - Tabular Model

Analysis Services - tabular model

3 day course

Other Microsoft Excel training resources

Excel exercises

Excel exercises

We have the following exercises available to do with the Excel and Desktop Power BI tools

View Excel 2010, Excel VBA or Power BI exercises

Excel videos

Excel videos

View Excel VBA, Microsoft Excel or Excel 2013 Power BI Tools videos

Excel skills assessment

Excel skills assessment

View Excel or Excel VBA skills assessment tests

Excel blogs

Excel blogs

View Excel, Power BI - Excel 2013 or VBA macros blogs

This page has 0 threads Add post