Excel Data Transformation

Learn to use the tools designed specifically to make transforming and loading data into Excel, streamlined and easily repeatable.

Rather than loading your raw data into a worksheet and then using a combination of formulas, VBA and manual hacking to get it into a useable state, Power Query makes it easy to create repeatable processes to do this that take place every time that you ‘refresh’ your connection. Data can come from a variety of sources and be transformed and combined in a number of ways. This is a tool that can save you hours!

Key content

  • Simple data transformations with text and dates, filtered rows, selected columns
  • ‘Unpivot’ data into a normalised format suitable for Pivot Table analysis
  • Combining data from a number of sheets in the same workbook
  • Combining data from a number of files in the same folder into a single table
  • ‘Unstack’ data from multiple sets of columns
  • Merge or Append data from different sources before loading into Excel
  • Automate refreshment of data
  • Editing the query language ‘M”
  • Creating a ‘relative’ file path option for these queries

Objectives

  • Understand the process of transforming raw data using Power Query
  • Be able to load transformed data into Excel for analysis and refresh it
  • Work with the Query Editor to refine the process

Prerequisites

Excel Essentials

Alternatively, for University of Auckland and UniServices staff, please access the following Online learning courses to ensure you meet the minimum competency requirements for this workshop:

  • Microsoft Excel 2016 Essentials: Creating, Editing, and Saving Workbooks
  • Microsoft Excel 2016 Essentials: Formatting Data
  • Microsoft Excel 2016 Essentials: Charts, Tables, and Images
  • Microsoft Excel 2016 Essentials: Formulas and Functions

In addition, you may like to complete the following recommended online courses:

  • Microsoft Office 2016 Intermediate Excel: Working with Data
  • Microsoft Excel 2016 Advanced: Accessibility, Transforming Data, and Errors

Suitable for

This is an intermediate level workshop suitable for those who have completed the Excel Essentials workshop, prescribed online learning courses or who are competent with the prerequisites topics covered. Before registering for this workshop, it is your responsibility to ensure that you are competent with all the prerequisite topics.Notes

Notes

PC only as this feature is not implemented in Mac versions of Excel. Excel 2016 is used. Also suitable for 2013 users although the Power Query add in will need to be installed in this version.

Cost

Free for University of Auckland and UniServices staff

Registration

If you are University of Auckland staff, please visit Career Tools to view workshop availability and register on a session.

For all others, please see our Registration page for more information.

Please be aware that a Cancellation policy applies to these workshop sessions. 

Dates, times and venues for scheduled workshops 2019

Monday 11 March 2019, 1-4pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

Tuesday 30 April 2019, 1-4pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

Wednesday 12 June 2019, 9am-12pm (FULL)
Room 305, Level 3, 49 Symonds Street, Auckland

Other resources

  • Online Learning - access online learning from anywhere at anytime in Career Tools.
  • Online Learning Place - register for a space in our Online Learning Place to complete your online learning without distractions, in your own time and at your own pace.