Excel Power Pivot and Power Query

Learn to utilise the features of PowerPivot and Query to summarise large amounts of data and streamline the process of presenting the results.

Power Pivot and Power Query for Excel supports BI (Business Intelligence) functionality by allowing large amounts of data from various sources to be prepared and stored in an Excel workbook. Once there, data can be integrated using relationships and analysed in a number of ways including the use of PivotTables and interactive displays such as PowerMap or PowerView (not the main focus of the course). Using PowerPivot for SharePoint you can share your workbooks across your team or publish them to the Web (not covered).

Key content

  • Use Power Query to ‘clean’, filter, transform and merge data
  • Deal with large data sets from a variety of sources in Power Pivot
  • Review the use of the Data Model to link tables of information
  • Begin to explore the Data Analysis Expressions (DAX) formulas PowerPivot uses
  • Deal with dates


  • Learn to utilise aspects of Power Pivot and Query as a BI tool


Excel Pivot Tables

Suitable for

This is an advanced level workshop which is suitable for those who have completed the prerequisite or who are competent with the topics covered in that workshop. Before registering for this workshop, it is your responsibility to ensure that you are competent with the topics covered in the prerequisite workshop. 


Jose Entrican

Evolve Capabilities

Scholarship / Professional Development

Leadership Framework dimension

Enabling People


PC only. Mac Excel 2011 or 2016 does not support this feature. Excel 2013 is used. Also suitable for 2010 users. Power Query is an additional download for 2013.


Free for University of Auckland and UniServices staff
Free for University of Auckland postgraduate students
$125+GST for others

Dates, times and venues for 2017

Session dates for July onwards will be posted here in early June.

January - March 2017                                                    

Registration is now open for the following session:

Thursday, 2 March 2017, 1-4pm 
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

April - June 2017

Wednesday, 31 May 2017, 9am-12pm
Room 305, Level 3, Building 620, 49 Symonds Street, City Campus

To enrol (University of Auckland Staff Only)

You can enrol through Career Tools.

  1. Visit Career Tools.
  2. Enter the course title e.g. Word Intermediate in the search box in the top right hand corner.  
  3. Click on the title of the course you would like to attend from the list.
  4. Find the session you would like to attend in the list at the bottom and click on Request to register.

If you do not have access to Career Tools, please contact the Staff Service Centre at 86000 or email Staff Service Centre

If you can no longer attend this session, please withdraw immediately using Career Tools by clicking Withdraw under My Actions against the respective session or go to Learn > View Your Training Record and select Withdraw from the drop-down menu to the right of the respective session. No shows and late cancellations may incur a fee. Please see our Cancellation Policy for details.

To enrol (all others)

Please refer to the enrolment instructions on the How to enrol page.