logo


your one source for IT & AV

Training Presentation Systems Services & Consulting Cloud Services Purchase Client Center Computer Museum
Arrow Course Schedule | Classroom Rentals | Student Information | Free Seminars | Client Feedback | Partners | Survey | Standby Discounts

Excel Dashboard Building Level 2: SMART DATA Analysis Strategies using Excel Power Query & Power Pivot

SS Course: 9000366

Course Overview

TOP

This hands-on, 1-day course is designed for business professionals and individuals interested in learning a SMARTer way to build advance business dashboards that perform querying, extracting, transforming and loading of data using Excel Power Query and data modeling using Excel Power Pivot

                                                                  

Scheduled Classes

TOP

What You'll Learn

TOP
  • Understand the basics of the SMART DATA Analysis & Dashboarding System for Microsoft Excel Workbooks
  • Understand the fundamentals of data querying and data modeling concepts
  • Understand how Excel Power Query works within the Excel Workbook environment
  • Understand how to use Excel Power Query extract, transform and load data into Excel Workbooks
  • Understand how Excel Power Pivot works within the Excel Workbook environment
  • Understand how to load data into the Data Model
  • Understand how to define relationships between multiple data tables
  • Understand how Excel Power Query & Power Pivot works with Excel Pivot Tables, Charts & Slicers

Outline

TOP
Viewing outline for:

Module 01) Exploring Business Dashboards built using Excel Power Query & Power Pivot Capabilities

a) Understanding the Dashboard Components

b) Using the Dashboard to answer business questions

c) Reviewing business requirements for a dashboard

d) Exploring how the Excel Power Query can be used to build Business Dashboards

e) Exploring how the Excel Power Pivot can be used to build Business Dashboards

Module 02) Getting Started with Data Querying Concepts of Excel Power Query

a) Data Workbooks

b) Data Sources

c) Data Connections

d) Data Queries

e) Data Results

Module 03) Getting Started with Excel Power Query Environment

a) Power Query Editor

b) Menu Tabs

c) Queries Panel

d) Query Results

e) Query Settings

f) Query Steps

Module 04) Getting Started with Basic Excel Power Query Operations

a) Extracting Data

b) Manage Columns

c) Sort Data

d) Transform Data

e) Adding New Data

f) Calculating Data

g) Loading Data

h) Analyzing Data with Pivot Tables and Pivot Charts

Module 05) Getting Started with Data Modeling Concepts of the Excel Power Pivot

a) Data Workbooks

b) Data Model

c) Data Tables

d) Data Relationships

e) Data Calculations

f) Data Hierarchies

g) Data Analysis

Module 06) Getting Started with the Excel Power Pivot Environment

a) Power Pivot Editor

b) Menu Tabs

c) Data View

d) Diagram View

e) Data Tables

Module 07) Getting Started with Basic Excel Power Pivot Operations

a) Importing Data

b) Managing Data Tables

c) Defining Relationships

d) Modifying Data Column Names

e) Adding Data Columns

f) Analyzing Data with Pivot Tables & Pivot Charts

Hands-On Labs Outline

After completing this course, students will have performed the following labs:

Exercise 01) How to build a Basic Business Tracking Dashboard Using Excel Power Query

a) Review business requirements for business tracking

b) Extract, Transform & Load data using Excel Power Query

c) Working with the Excel Data Table

d) Working with the Data Slicer

Exercise 02) How to build a Business Summary Dashboard Using Excel Power Query, Power Pivot & Pivot Tables

a) Review business requirements for business metrics

b) Extract, Transform & Load data using Excel Power Query

c) Model data using Excel Power Pivot

d) Working with the Excel Pivot Table

e) Working with the Data Slicer

Exercise 03) How to build a Business Financial Dashboard Using Power Query, Power Pivot & Excel Pivot Chart

a) Review business requirements for business trends

b) Extract, Transform & Load data using Excel Power Query

c) Model data using Excel Power Pivot

d) Working with the Excel Pivot Table

e) Working with the Excel Pivot Chart

f) Working with the Data Slicer

Prerequisites

TOP

Who Should Attend

TOP
Business professionals and individuals interested building dashboards.

Next Step Courses

TOP