Description

Learn how to work with and connect multiple data sets to effectively analyse and report on data.

Course overview

Duration: 1 day (6.5 hours)

Within Excel you have some powerful features to enable you to connect and analyse multiple data sources. Power Query enables you to import and manipulate your data, Power Pivot enables you to connect multiple data sources and create pivot tables and pivot charts from them.

This course is an introduction to Power Query and Power Pivot in Excel to get you started on creating a powerful reporting capability.

Knowledge of working with Excel workbooks and relational databases would be an advantage.

Objectives

By the end of the course you will be able to:

Import data from multiple data sources
Edit and transform data before importing
Add extra columns of data
Append data
Merge data from other tables
Create data models
Build data relationships
Build Pivot Tables
Build Pivot Charts
Use Slicers and Timeline Filters

course image

Content

Importing data

Data sources
Importing data

Transforming data

Editing your data
Setting data types
Removing columns/rows
Choosing columns to keep
Setting header rows
Splitting columns

Appending queries

Appending data from other tables

Adding text

Columns from example
Custom columns
Conditional columns

Merge queries

Setting up and using merge queries
Merging in columns of data

Creating a data model

The data model
Multiple data tables
Connecting tables
Building relationships
Relationship types
Building visuals from multiple tables

Analysing information using pivot tables

Creating and modifying a Pivot Table
Recalculating the Pivot Table
Filtering the Pivot Table
Searching the Pivot Table
Drilling down to underlying data
Customising field names
Changing field formatting

Pivot charts, slices and timelines

Creating Pivot Charts
Adding and using Slicers

Course Images

Excel Power Query and Power Pivot

By Underscore Group

Booking options

Highlights