This two day intensive workshop will provide you with the knowledge to make best use of Power BI and Excel’s Business Intelligence tools to mash up, analyse and visualise your data.

By the end of this course you will be able to use Excel Power Pivot tools confidently. Many of the topics covered are only available in Office Professional Plus 2013 or higher. If you have an earlier version we do not recommend taking this course.

Download the course details
  • Create a PowerPivot 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 PowerView, PowerMaps and Power Query tools

It is recommended that you have completed either the Excel Intermediate or Excel Advanced level workshops, or have equivalent experience.

It is expected that you have a good understanding of Excel functions such as  IFs and VLookups as well as Defined Names, Importing Data and a basic understanding of PivotTables.

Our courses run from 9.30 am to approximately 4.15pm. While you are with us we are focused on offering you a relaxed and productive learning environment. When you book a course with us you get the following:

  • A full colour training manual/book.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away.
  • Certificate of attendance
  • Freshly prepared lunch.
  • Refreshments throughout the day.
  • Training in fully air conditioned facilities from an experienced instructor.
  • What is BI?
  • Main Concepts
  • Power Query
  • Power Pivot
  • Power BI Desktop
  • Power Query
  • Working with CSV files
  • Working with Text Files
  • Working with Excel Files (mini meta data)
  • Working with other data sources
  • Meta Data Query
  • File Content Query
  • 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 Custom Column
  • Loading Data
  • Loading Data into a Worksheet
  • Loading Data into the Excel Data Model
  • Refreshing Queries Manually and Automatically
  • Query Concepts
  • ETL
  • Get & Transform
  • Creating and editing Simple
  • Query
  • Get from Folder
  • Query vs Source Data
  • Writing ‘Robust’ Queries
  • 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 Hierarchies
  • 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
Dates & Prices

This course is run by arrangement for private groups and 1-2-1 sessions.

Please contact us for more information

01483 688488

Contact us

Have a question?

We're here to answer them

01483 688488

Contact us
We’re trusted by