Course
Excel Business Intelligence using Excel Power Tools
Course Duration
1 Day
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.
Public Scheduled Dates
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.
<<
<
>
Public scheduled prices per delegate +VAT
Private courses start from £745 for up to 8 delegates
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
What's included
-
- 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 PivotBuilding 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 SlicersExcel 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
"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
Other Courses In This Series
Microsoft 365 Copilot for Business Users - Introduction This one-day course is designed to help business users understand the basics of Microsoft Copilot for Microsoft 365, a productivity tool that uses artificial intelligence to provide real-time intelligent assistance within Microsoft 365 productivity apps including Word, Excel, PowerPoint, Teams and O
Excel Advanced Macros 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 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 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 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 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 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 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
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 class sizes. Course materials included. Flexible course content. Expert trainers and tried & tested training methods. Course materials available online using your personal training account.
You can master Excel, please contact us to book a place or request a quote.
-
Course Options
Please contact us to discuss options for this coursePublic ScheduledPublic ScheduledFixed 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!PrivatePrivateFlexible 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.BespokeBespokeBespoke 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.VirtualVirtualThese courses can be attended from anywhere.
- Virtual Training
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:4/20/2024 2:58:52 AM
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:4/20/2024 2:58:52 AM