Microsoft Excel: The Data Model - Power Pivot & Power Query

Data ModelPower Pivot and Power Query are business analytics tools that transform how you work with data, by automating repetitive tasks and analysing data from a variety of sources without using Excel functions or formula.

The Data Model and Power Pivot combine several Excel tables by forming relationships between them and then analyising all, or some of the data, in a single Power Pivot table.   

Power Query allows tables to be imported from another source, cleaned, and then used.

Power Query (called: Get & Transform from 2016) allows data from multiple sources (e.g. internet, Excel, Access, CSV files, text files, external databases SQL etc.) to be downloaded and then transformed (by adding or deleting columns, cleaning data and adding calculations).  The data can then be analysed in an Excel table, Pivot Table or added to a Data Model.  All this analysis is done without using any Excel formula.  

Power Pivot is relatively simple to use, saves learning complex formula, and automatically updates new data.

Power Query is also part of Power BI.  Allowing this workshop to serve as an introduction to the Power BI workshop to be offered later.

Skills Introduced:
This workshop will cover examples similar to the following:

  • Example 1: A business has several Excel tables that contain information about orders, clients, products and salespeople. A Data Model is established using Power Pivot and relationships formed between the various tables so the data required can be selected from all of the tables and viewed in a single Power Pivot The following month’s data can be easily updated.
  • Example 2: A business wants to import several CSV files from an external database containing information about its clients, sales and products. Power Query is used to import, select, and clean the data, and combine it into a single table, which is then analysed in a Power Pivot Table.  The following month’s data is added and is automatically updated.

Skills needed: 
This is an Intermediate/Advanced level workshop.  Attendees need to be familiar with Excel tables and have some basic knowledge of Pivot Tables.  

If unsure, please contact the trainer to discuss whether the workshop is suitable for you.

Read about the trainer, Bernard Pfister from Excel Training Taranaki, and other workshops here


Requirements:
Power Query is available in Excel 2016, 2019 and Microsoft 365. (It is available as a downloadable add-in for 2010 and 2013 from the Microsoft site. If you decide to download the add-in please ensure that this is done before the workshop).  To check your version of Excel, open a spreadsheet, click on File, then Accounts.

Attendees must bring their own laptops (not Tablets) with full Microsoft Excel, version 2010 or higher.  To check version, open a spreadsheet, click on File then Account.  
Please inform the training provider if you will be bringing a Mac laptop

This workshop is interactive and attendees will gain hands-on experience.  Raw data to work and detailed notes will be provided.


Register to attend below

Limited spaces to ensure student-centred learning.

Tea, coffee, water and light refreshments will be provided.

For more information, contact Julia at julia@taranakichamber.co.nz, or call 06 759 9080 

Date:
Wednesday, 7th September, 2022
Time:
9:30 a.m. to 11:30 a.m.
Location:
42 Egmont Street, New Plymouth
Single Ticket (Member) incl GST:
$159.00
Single Ticket (Non-Member) incl GST:
$189.00

Step 1 of 4 - Event Details

Membership

Choose if you are booking as a member or non-member

First time here? If you’re a current Chamber member and this is your first time registering for an event on our new website, please click here and enter your email address to be sent your password.

Forgotten Password

Event


Total (incl GST)

Registration Policy:
Payment is required prior to course attendance, and your registration will be confirmed once payment has been made. 

Cancellation Policy:
Any cancellation and/or changes must be made in writing to the Taranaki Chamber of Commerce no less than ten working days prior to the course in order to receive a full refund/credit. No refund or credit will be given for cancellations made after this or for non-attendance at the course.


Taranaki Chamber of Commerce Covid-19 Policy:

  • All Taranaki Chamber of Commerce events follow Government and Ministry of Health guidelines.
  • If you are feeling unwell with cold or flu symptoms, please stay at home.
  • The wearing of face coverings is recommended.