Power Query and Power Pivot in Microsoft Excel:

A Quickstart Guide

Course Description

Power Query and Power Pivot are two tools available in Microsoft Excel. If you are serious about data analysis, then you need to know how to use these tools. This Quickstart guide will give you the skills you need to get started with these powerful features.

The Power Query tool allows you to import external data and then modify it to meet your needs, before loading it into Excel. These editing tasks are all things you can do manually in Excel, but Power Query greatly speeds things up when you have a large volume of data.

The Power Pivot tool allows you to connect multiple data tables in an extended pivot table. Power Pivot also introduces new analysis and query tools to further enhance data analysis.

If data analysis is your thing, then this course will help you get up to speed with these feature-filled tools. Through screen-capture video, the course provides a step-by-step guide to the foundation skills needed to use both Power Query and Power Pivot effectively. The course is designed to make your first steps into Power Query easy and to build your confidence to use them on your own.

Both Power Query and Power Pivot are also available in Microsoft Power BI – the data visualisation suite. Once you have learned how to use Power Query and Power Pivot in Excel with this course, you’ll have a head start in getting to grips with Power BI

Get up to speed with Power Query and Power Pivot in Microsoft Excel with this Quickstart guide.

Key Learning Points

On completion of the course, delegates will be able to:

Use Power Query to load data from an external source into Microsoft Excel
Clean and manipulate external data before loading it into Excel
Consolidate separate files with Power Query
Update Excel tables with new data in the same format
Use Power Query to “unpivot” report tables to prepare the data for analysis
Explain the core functionality of Power Pivot
Link data tables together in the Power Pivot data model
Create pivot tables that draw data from several source data tables
Create performance measures and new analysis in the Power Pivot data model
Use the most popular DAX query language functions in Power Pivot

Curriculum

Lesson 1: Power Query Quickstart

Lesson 2: Consolidating Files with Power Query

Lesson 3: Unpivoting a Table

Lesson 4: Power Pivot Quickstart

Lesson 5: Power Pivot – Adding Data and Functionality

Lesson 6: Key DAX Functions for Power Pivot

Lesson 7: The DAX CALCULATE Function

Lesson 8: Key Learning Points

Pre-Course Requirements

There are no pre-course requirements

Additional Resources

Starter spreadsheets for each lesson are provided so that learners can follow the steps presented.

Course Tutor

Your tutor is Ross Maynard. Ross is a Fellow of the Chartered Institute of Management Accountants in the UK and has 30 years’ experience as a process improvement consultant specialising in business processes and organisation development. Ross is also a professional author of online training courses.

Ross lives in Scotland with his wife, daughter and Cocker Spaniel

Questions

What is the main feature of Power Query?

The ability to link data tables together to create pivot tables that allow data to be drawn from any and all of the source tables
The ability to clean and manipulate large volumes of external data efficiently and to load the result into Microsoft Excel
The removal of Microsoft Excel’s restrictions on file sizes and the number of rows of data allowed
The ability to visualise data in dashboards and management reports

What is the main feature of Power Pivot?

The ability to link data tables together to create pivot tables that allow data to be drawn from any and all of the source tables
The ability to clean and manipulate large volumes of external data efficiently and to load the result into Microsoft Excel
The removal of Microsoft Excel’s restrictions on file sizes and the number of rows of data allowed
The ability to visualise data in dashboards and management reports

Which of the following actions cannot be performed within Power Query?

Split text into multiple columns
Trimming text and removing spaces
Creating calculated columns using DAX functions
Make date based calculations

Which of the following statements is true?

You can use Power Query to combine files that have the same format and open them all in the same spreadsheet
You can use Power Query to link data tables and create pivot tables that pull data items from all the linked tables
You can use Power Query to create interactive dashboard reports
You can use Power Query to export data files to Microsoft Access and other databases

Which of the following provides a compelling case to use Power Pivot in Microsoft Excel?

The data visualisation and presentation features of Power Pivot are much more powerful than those in Excel
Power Pivot offers the ability to clean large files of data imported from other systems quickly
Existing reports and tables can be “reverse engineered” (unpivoted) to create data tables for more advanced analysis
Files sizes in Power Pivot are smaller than in Excel and there is no limit on the number of rows of data that can be stored. This makes it much faster and more efficient to use Power Pivot for large data sets

Power Pivot uses analysis functions from the DAX formula language. What does “DAX” stand for?

Digital Architecture Extraction
Data Arbitrage Extrapolation
Display Analytics Extension
Data Analysis Expressions
Deeply Annoying Exercise

The DAX CALCULATE function is one of the most powerful and useful functions available in Power Pivot. Which statement best describes it?

It performs row by row calculations defined by the user and adds the total of them all together for a specified table
It performs row by row calculations defined by the user and displays the maximum result for a specified table
It performs a defined calculation and then applies filters specified by the user
It filters the data in a specified table according to criteria set by the user

As well as in Power Pivot in Microsoft Excel, in what application are DAX functions also available?

Microsoft PowerPoint
Microsoft Forms
Microsoft Teams
Microsoft Power BI

How do DAX functions differ from Microsoft Excel functions?

DAX functions are designed specifically for use with SQL based data sources
DAX functions are completely new and provide additional functionality within Microsoft Excel
DAX functions never take a cell reference or a range as reference, but instead take a column or table as reference
DAX date and time functions return a date as a serial number, whereas Excel date and time functions return a datetime data type

Power Query and Power Pivot in Microsoft Excel: A Quickstart Guide

Adventures in Excel