Request a callback
Email us

Business Booking Details

Payment Of Invoices

For bookings from established businesses, we do not require payment at the point of booking.

We will send you an invoice which you can submit to your accounts payable system.

Purchase Orders

If your employer uses a purchase order (P.O.) system to manage supplier invoices that is no problem.

  • If you already have a P.O. number when booking add it to the PO number box on the booking form, and we will include it on your invoice.
  • If you need an invoice to generate a P.O. number in your accounts system, please book the course as normal and add a note in the PO number box – to be provided
  • We will send you an invoice (without a P.O.) which you can use to generate a P.O..
  • We will then re-issue your invoice with your P.O. included when you provide us with it

PAYMENT METHODS:

You are not able to pay directly online at the time of booking, we issue an invoice for all bookings.

We accept payment either via direct bank transfer or card

Our invoices contain our bank details if you would like to pay by bank transfer and there is also information on how to pay by card.

Please note, if you are using a business card there is a surcharge of 2% to cover the payment processing charges.

Excel BI Training: Power Query & Power Tools Course

Excel BI Training: Power Query & Power Tools Course

4.76
17 reviews
microsoft-partner
feefo award

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 Excel 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 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

Online Training Requirements

To attend this Excel course online, you will need:

✔ MS Excel on your Windows PC with a camera, speakers & microphone
✔ A stable internet connection capable of running Zoom
✔ To be a confident computer user and able to use Zoom

If you have access to a second screen, we would encourage you to use it as it improves the experience.