Introduction

Master Excel’s suite of business intelligence tools (Power Query, Power Pivot, Power Maps etc) on this 2-day Excel training course.

This is a hands-on course that includes lots of time to experiment with what you are being taught and ask questions.

By the time you finish, you’ll be a confident Excel Power Query and Power Pivot user.

✔ A complete Excel Power Tools course that covers all of Excel’s BI functions.
✔ Plenty of one to one attention and time to ask questions.
✔ Friendly expert trainers, small groups and a comfortable place to learn.
✔ Ongoing support and help with issues you have after the course.

What Will I Learn?

By the end of this course you will be confidently using Excel’s business intelligence Power Tools to analyse data.

You’ll be very comfortable using Microsoft Excel to:

Create a Power Pivot model by importing data from various sources
Understand and manage data relationships
Create and edit hierarchies
Insert calculated columns using Data Analysis Expression (DAX) formulas
Insert, modify, and format PivotTables and PivotCharts
Create and edit Measures (DAX aggregate functions)
Create report dashboards using slicers, timelines and KPIs
Use Power View, Power Maps and Power Query tools

The Trainer

Oakley is our usual trainer for this course. He has been a Microsoft certified trainer for 8 years.

He has been training Microsoft applications for 19 years and focuses on Excel BI, Power BI and Project.

Am I Ready For This Course?

You don’t need any prior experience with Excel’s Power Tools to attend this course.

It is recommended that you have completed our Excel Advanced level workshop, or have equivalent experience.

It is expected that you have a good understanding of Excel functions such as IF and VLookup functions, as well as Defined Names, Importing Data and a basic understanding of Pivot Tables.

What will I need to attend remotely?

You will need the following installed and fully working to be able to participate in the training:

PowerBI
64bit version of Excel
microphone
webcam

The Training Day

You don’t need to bring anything with you. We include everything that you’ll need for a really fun, productive day of training.

Our courses run from 9.30am to roughly 4.15pm with refreshments and biscuits throughout the day and a break for lunch.

We offer a relaxed, supportive learning environment, fully air-conditioned training facilities and some of the nicest instructors on the planet.

Also, you’ll receive:

✔ A full-colour A4 manual covering everything in the course to keep.
✔ A USB stick so that you can take your course exercises home with you.
✔ A Certificate of Attendance.

Course Details

Download full course details

This session introduces the course looking at why you might be using Power Query or other Excel Power Tools.

What is BI?
Main Concepts
Excel Power Query
Excel Power Pivot
Power BI Desktop

During this section we look at importing data for data analysis from a number of data sources.

Working with CSV files
Working with Text Files
Working with Excel Files (mini metadata)
Working with other data sources

Meta Data Query
File Content Query

We now move to refining our data for use in our data analysis. This section focuses on rows, while the next section focuses on columns.

Filtering out unwanted Data
Using Auto-Filter (problems)
Using Number, Text and Date Filters
Removing Duplicate Values
Filtering out Rows with Errors
Sorting

Moving / deleting Columns
Splitting Columns
Merging Columns

Text
Number
Date/Time
Locale

Unpivoting Columns to Rows
Transposing a Table
Creating a Custom Column
Loading Data
Loading Data into a Worksheet
Loading Data into the Excel Data Model
Refreshing Queries Manually and Automatically

We now look at how to use power query.

Query Concepts
ETL
Get & Transform
Creating and editing a Simple Query
Get from Folder
Query vs Source Data
Writing ‘Robust’ Queries

This section looks at using Microsoft Excel Power Pivot and how to analyse new data using it.

What is Power Pivot?
Pivot Table Examples with Excel
Data
Power Pivot Examples with a Data Model

Working with Excel files
What is in an Excel file?
Working with Excel Tables
Working with Data Ranges

Understanding Acceptable Data Types
Adding Excel Tables to Power Pivot
Adding and Maintaining Data in Power Pivot

What is a Data Model?
Understanding Key Fields
Relationships in a Data Model
Data Hierarchy

Types of Calculations in Power Pivot
Creating a Calculated Column
Creating a Calculated Field (Measure)
Understanding DAX Measures
Rules and Best Practices
Choosing Between Calculated Columns and Measures

Why a Date (Calendar) Table is required
Generate a Date Table
Mark as a Date Table
Grouping dates for time analysis
Time Intelligence functions

Dashboard & Report principles
Filtering Data using Slicers
Creating Power Pivot Charts
Formatting Power Pivot Charts
Using Multiple Charts and Tables
Connecting Slicers

Getting Data
Data Model
Time Intelligence
Visualisations
Spatial Intelligence
Sharing Reports

Analyse typical user file, tasks, outcomes
Set up data model based on real-life file
Discuss and create analytics for the model

Excel BI Training: Power Query & Power Tools Course

Introduction

What Will I Learn?

The Trainer

Am I Ready For This Course?

What will I need to attend remotely?

The Training Day

Course Details

Business Intelligence

Common Data Import Sources

Understanding The Query

Filtering And Sorting Rows

Working With Columns

Changing Data Types

Table Transformations

Introduction To Power Query

Understanding Power Pivot

Working With Data

Importing Data Into Power Pivot

Creating A Data Model

Using Calculations In Power Pivot

Time Intelligence

Data Visualisation - Excel's Visualisation Tools

Power BI Desktop

Power BI (.com) Workshop

Book this course