The Excel Data Model, Power Pivot and Power Query

Introduction:

This workshop is an introduction to the Data Model, Power Pivot and Power Query which are business analytics tools that can transform the way you work with data by automating repetitive tasks and analysing 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 analyising 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 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 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.

 

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

 

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, 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 the trainer 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.