Training Course

Excel Business Intelligence using Excel Power Tools

Download Course PDF

Public Scheduled Course Dates

Prices per delegate. All prices exclude VAT.
Date Location Price
16 Sep 2024
Virtual
£395
25 Nov 2024
Durham
£445
23 Jan 2025
Virtual
£395
09 Apr 2025
Virtual
£395
25 Jul 2025
Virtual
£395
15 Sep 2025
Virtual
£395
24 Nov 2025
Durham
£445

Private Training

Flexible dates, location and content. All prices exclude VAT.

Course Duration

1 Day

Excel Business Intelligence using Excel Power Tools Course Overview

This course is split into 3 main areas:

1. Get and Transform (Power Query)
2. Power Pivot - Data Models and DAX calculations
3. Dashboard Creation with Pivot Tables, Charts and Slicers

It is aimed at users who want to analyse data from multiple sources and want to create effective data dashboards.
This course will be delivered on Excel 2016 or above and using generic data files that aim to appeal to a wide audience.

Course Prerequisites

    • Delegates should have attended our Excel Level 3 course, or have equivalent knowledge.

Course Objectives

    • Confidently use the Get and Transform query tools
    • Write clever and effective queries to have data ready for analysis
    • Create simple, clear data models in PowerPivot
    • Use these data models to create pivot tables based on multiple tables
    • Build dashboards that give powerful insights and powerful visuals
    • Start to use the DAX language for more in-depth analysis of data
    • Create and display hierarchies and KPIs in Excel

Included with this course

    • Lunch and refreshments when training is delivered at our training venues
    • Comprehensive course materials and exercises
    • Telephone and Email Support

Excel Business Intelligence using Excel Power Tools Course Content

  • Get and Transform (Power Query)

    Introduction to Get and Transform
    Understanding the interface
    Create and Edit a simple query
    Common Data Import Sources
    Working with CSV, Text, Excel Files
    Other Sources
    Importing multiple files
    Working with Web data
    Working with Columns
    Name, Move, Split, Merge, Remove
    Filtering and Sorting
    Using Auto-Filter
    Sorting
    Using Number, Text and Date Filters
    Filtering Rows by Range
    Changing Values in a Table
    Replacing Values
    Transformations: - Text; Number; Date/Time
    Replacing Missing Values
    Table Transformations
    Unpivoting Columns to Rows
    Transposing a Table
    Creating Custom Columns
    Append Queries
    Two Data sets
    Multiple Tables
    Working with formula queries
    Loading Data
    Loading Data into a Worksheet
    Loading Data into the Excel Data Model
    Power Query and Table Relationships
    Refreshing Queries Manual & Auto

  • Power Pivot

    Building Data Models
    Create relationships between tables
    One-to-Many Relationships
    Deriving relationships from data sources with the Relationships tool and the Diagram View
    Hiding tables from View
    Excel Tables
    Why Excel Tables are important
    Using Slicers to filter data
    Adding Linked Tables to the Data Model
    Building DAX (Data Analysis eXpressions) Formulas
    Formula basics
    DAX Data Types
    DAX Operators
    Defining Calculated Columns
    DAX Measure Examples
    RELATED(), CALCULATE(), SWITCH()
    Working with Dates
    Why a Date (Calendar) Table is required
    Generate a Date Table
    Mark as a Date Table
    Grouping dates for time analysis
    Comparing and categorising time periods with Time Intelligence functions
    KPIs - Setting key business targets
    Analysing performance with calculated fields
    Gauging performance against goal

  • Dashboard Creation with Pivot Tables, Charts and Slicers

    Excel Pivot Tables Recap
    Understanding the structure
    Changing the Layout
    Using filters
    Grouping data for ad-hoc subtotals
    Creating PivotTables with PowerPivot
    What's different in a PowerPivot Pivot Table?
    Creating a PivotTable with Power Pivot data
    Using the Field list with the Data Model
    How Drill-down differs in Power Pivot
    Using Slicers to Filter Data
    Using Slicers
    Connecting Slicers to more than one PivotTable or Chart
    Dashboards
    Presenting and analysing data with PivotCharts
    Drilling down into data using a hierarchy
    Managing data with perspectives

 

Public Scheduled Course Dates

Prices per delegate. All prices exclude VAT.
Date Location Price
16 Sep 2024
Virtual
£395
25 Nov 2024
Durham
£445
23 Jan 2025
Virtual
£395
09 Apr 2025
Virtual
£395
25 Jul 2025
Virtual
£395
15 Sep 2025
Virtual
£395
24 Nov 2025
Durham
£445

Private Training

Flexible dates, location and content. All prices exclude VAT.
  • Call us on
    0191 377 8377
    to discuss your requirements.
    Mon-Fri 9am-5pm.
    Have a Question?
    Keep me up to date with offers and news
    By submitting this form, you consent to IT Training Solutions Ltd storing your information in order to respond to your request.
  • Excel Business Intelligence using Excel Power Tools Course Options
    Public Scheduled
    Public Scheduled
    Fixed course content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!
    Private
    Private
    Flexible course content, flexible training date(s), flexible location(s), variable course length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
    Bespoke
    Bespoke
    Bespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.
    Virtual
    Virtual
    These courses can be attended from anywhere.
  • Virtual Training

I found learning about data relationships and models particularly helpful. More generally, being able to build a report excel dashboard from scratch is going to change the way I work for the better.

N.B. DCC, Excel Business Intelligence using Excel Power Tools

Following this training what are you going to do differently I am going to use excel power tools with significantly more confidence and develop more intuitive dashboards.

N.B. DCC, Excel Business Intelligence using Excel Power Tools

The course flew by and the course convenor was very good at their job. Will definitely be using ITTS courses in the future.

N.B. DCC, Excel Business Intelligence using Excel Power Tools

Trainer was great! Very enthusiastic. Delivered extremely well and user friendly

D.B. Edinburgh Woolen Mill, Excel Business Intelligence using Excel Power Tools

The whole course has been beneficial and informative

N.E. Engie, Excel Business Intelligence using Excel Power Tools

Very good!

L.O. Labour Party, Excel Level 2

Are there any aspects of the course which you feel require improvement None, I feel greatly informed and had opportunity to discuss and ask what I wanted

S.R. J2 Research, Time Management

The course was very informative and engaging

R.D. EGGER UK Ltd, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

+ View More Training Reviews

 

Public Scheduled Course Dates

Prices per delegate. All prices exclude VAT.
Date Location Price
16 Sep 2024
Virtual
£395
25 Nov 2024
Durham
£445
23 Jan 2025
Virtual
£395
09 Apr 2025
Virtual
£395
25 Jul 2025
Virtual
£395
15 Sep 2025
Virtual
£395
24 Nov 2025
Durham
£445

Private Training

Flexible dates, location and content. All prices exclude VAT.

Other Courses In This Training Series

Excel Advanced Macros Course Course Overview: This course is designed to introduce you to the Macro environment of Excel, it will provide you with the confidence to record macros in Excel and then be able to gain a better understanding of the code and make changes to it. We even teach you some commands that cannot be recorded, such as how to sh Excel Level 3 Course Course Overview: This course will benefit those who are already up to speed with Microsoft Excel but would now like to automate some common tasks and apply advanced analysis techniques to more complex data sets. This Excel course is aimed at anyone desiring to gain the skills necessary to create macros, audit and an Excel Level 1 - Essentials Course Course Overview: Microsoft Excel is a spreadsheet program that can be used for storing, organising and manipulating data.This Excel introductory course will enable delegates to gain a good general understanding of the main features of Microsoft Excel, giving them the ability to produce standard spreadsheets, create Excel Level 2 Course Course Overview: In Microsoft Office Excel Level 1, delegates created, edited, formatted, and printed basic spreadsheets. This course will enable delegates to streamline repetitive tasks and display spreadsheet data in more visually effective ways. The target students for this course are students who desire to gai VBA Essentials using Excel Course Course Overview: This course is aimed at those people who find they have reached their limit with the menu items of Excel and need to modify Excel to be better suited to their environment, by creating their own functions and procedures. Excel Analysing and Presenting Data with Dashboards and Pivot Tables Course Course Overview: This outline below contains an overview of a wide range of features within Excel that when used together can be used to create interactive visual reports and dashboards. The course will show delegates how these features can be used individually to present data but can also be used in combination to Excel Formulas and Functions Indepth Course Course Overview: This course is designed to help users get the most from the advanced functions in Excel. Advanced Lookup and Reference functions to help you gather data, Text functions to organise and validate the data, Logical functions to analyse the data. You will use the advanced features of Microsoft Excel to

 

 

Please contact us to discuss your Excel Business Intelligence using Excel Power Tools course, we have many solutions available to ensure you receive the best training possible.
Your Excel course can be delivered online or in-person.  You can choose your Excel path with introduction to advanced levels. Master Excel Business Intelligence and Power Tools, VBA and more.  Learn in-person or online with our virtual classrooms.  Your course can be delivered online, at your office or at one of our training venues, it's your choice. Small course sizes. Course materials included. Flexible Excel Business Intelligence using Excel Power Tools course content. Expert trainers and tried & tested training methods. Many course materials available online using your personal training account.  You can master Excel, please contact us to book a place or request a quote for your Excel Business Intelligence using Excel Power Tools course.

General Information

Microsoft and Microsoft Excel are Registered Trademarks An overview of Power BI Tools using Excel - Power BI with Excel training course available in the North East including Newcastle, Durham, Washington, Darlington, Sunderland, Gateshead, Leeds, Tyne and Wear, Middlesbrough and throughout the North East. Power BI using Excel - Training Courses available in Newcastle, Darlington, Durham, Washington, Sunderland, Leeds, Middlesbrough, Tyne and Wear, Gateshead and throughout the North East. Power BI Courses in the North East.
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:9/14/2024 5:06:03 AM