We use cookies to offer you a better experience and to analyse how visitors use our site, you may consent to our cookies by continuing to use our site, or you may view more information and choose to reject cookies by clicking here. Thank you

Contact us online or call us on 0191 377 8377

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields

PDF
Course Excel 2016 - Business Intelligence using Excel Power Tools
  • Public Scheduled Price£495 +VAT
  • Course Duration2 Days
  • Course Options
    Public Scheduled
    Public Scheduled
    Fixed 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 content, flexible date, flexible location, variable 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.
  • Latest Reviews
    "The whole course has been beneficial and informative"

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

Overview
This course is split into 4 main areas across the 2 day duration:

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

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 and using generic data files that aim to appeal to a wide audience.


Prerequisites
    • Delegates should have attended our Excel Level 3 course, or have equivalent knowledge.
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
    • Plot geographical data on to maps and create based visualisations
What's included
    • Laptops are available for training delivered at client's premises
    • Lunch and refreshments when training is delivered at our training venues
    • Comprehensive course materials and exercises
    • Telephone and Email Support
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
    • Power View and 3D Maps
      Power View
      What is it for?
      Enable the Power View add-in

      Filtering Data
      Add Filters to different data types
      Visualisation-Level Filters

      Charts
      Create Charts
      Drilling Down with Charts
      Multiple Charts
      Scatter and Bubble Charts

      Interactive Data Selection
      Tiles
      Slicers
      Charts as Filters

      3D Maps (Power Maps)
      Geocode data
      Understand supported geographical formats
      Changing geographical fields
      Fixing common geocoding errors

      Create a Power Map
      Adjust Map Display
      Multi-Value Series
      Add Tiles to Maps
      Drilling Down Maps

      Map Types
      Bubble, Column, Heat
      Using Region charts to create territories

 

Latest Course Reviews
"The whole course has been beneficial and informative"

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

 

IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk

Printed: 17 Jun 19

 

 

 

Contact us

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields