Introduction to: Excel Data Model, Power Pivot & Power Query

Introduction to workshop:

The Data Model, Power Pivot and Power Query are business analytics tools that can transform the way you work with data by automating repetitive tasks and analyzing large (or small) amounts of data from a variety of sources without using Excel functions or formula.

The Data Model and Power Pivot can be used to combine several Excel tables by forming relationships between them and then analyzing all or some of the data in a single Power Pivot table. (If the tables have to be imported from another source and have to be cleaned, then Power Query can be used.)

Power Query (called: Get & Transform from 2016) gives added capability in that it 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 analyzed 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 and saves learning complex formula and it automatically updates new data.

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


Workshop content:

This workshop will cover the following two examples:

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 table. 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 analyzed in a Pivot Table. The following month’s data is added and is automatically updated.


Workshop 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 you come to the workshop). To check your version of Excel, open a spreadsheet, click on File, Accounts.


This is an Intermediate/Advanced level workshop and so you should be familiar with Excel tables have some basic knowledge of Pivot Tables. You are welcome to contact me to discuss whether the workshop may be suitable for you.

This two hour workshop is offered at the Taranaki Chamber of Commerce's training room from time to time and is also available for individual businesses at their premises, and can be adapted to suit their needs.