EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

55164AC & 50433A- Quick Powerful Graphics with Power View, PowerPivot, Power Query, Power Map and Power BI & Power Pivot for End User

Course outline

4 days
Discuss this programme →

Duration

4 days

About This Course

This course is designed to teach the participant how to combine the functionality of Power View, PowerPivot, Power Query, and Power BI into graphs, charts, KPIs, reports, and other visualizations for use in their business. This course is lab example intensive. Besides, it also teaches how to use PowerPivot to access data sources, create relationships, use the PowerPivot DAX expressions and generate and format reports. You will also explore how PowerPivot workbooks can be integrated with SharePoint 2010.

Audience Profile

  • Business analysts, business intelligence developers, and reporting professionals.
  • Advanced Excel users, users that want to gain familiarity with PowerPivot to build compelling reports.

At Course Completion

  • Navigate the Power View interface.
  • Utilize Power View reports.
  • Work with tables.
  • Understand aggregations.
  • Utilize matrix.
  • Configure drill through.
  • Use cards as a visualization.
  • Apply filters.
  • Create charts.
  • Utilize tiles.
  • Add slicers to reports.
  • Utilize themes.
  • Navigate the PowerPivot interface.
  • Load data.
  • Examine and configure relationships.
  • Configure calculated columns are calculated fields.
  • Create PivotTables.
  • Understand and utilize DAX.
  • Use KPIs.
  • Work with hierarchies.
  • Manage perspectives.
  • Navigate the Power Query interface.
  • Load data.
  • Filter data.
  • Merge datasets.
  • Manage modifications.
  • Perform data cleaning.
  • Navigate the Power Map interface.
  • Use Bing maps.
  • Examine map data.
  • Configure tours, scenes, and layers.
  • Add time to a presentation.
  • Navigate the Power BI interface.
  • Utilize data sources.
  • Understand querying with natural language.
  • Explore every aspect of using Microsoft PowerPivot.

Course Details

Module 1: Course Overview

This module explains how the class will be structured and introduces course materials and additional administrative information.

Lessons

Introduction

  • Course Materials
  • Facilities

Prerequisites

  • What We'll Be Discussing

Lab 1: Course Overview

  • None

After completing this module, students will be able to:

  • Successfully log into their virtual machine.
  • Have a full understanding of what the course intends to cover.

Module 2: Power View

In this module, we will explore Power View and its capabilities, then briefly walk-through the types of reports available and how to format them.

Lessons

  • Exploring the Power View Interface
  • Visualizing Power View Reports
  • Working with Tables
  • Understanding Aggregations
  • Utilizing Matrix
  • Configuring Drill Through
  • Using Cards
  • Applying Filters
  • Creating Charts
  • Examining Tiles
  • Adding Slicers to Reports
  • Theming Reports

Lab 1: Power View

  • Power View Interface
  • Working with Tables
  • Aggregations
  • Matrix
  • Drill Through
  • Cards
  • Filters
  • Charts
  • Tiles
  • Adding Slicers to Reports

After completing this module, students will be able to:

  • Navigate the Power View interface.
  • Utilize Power View reports.
  • Work with tables.
  • Understand aggregations.
  • Utilize matrix.
  • Configure drill through.
  • Use cards as a visualization.
  • Apply filters.
  • Create charts.
  • Utilize tiles.
  • Add slicers to reports.
  • Utilize themes.

Module 3: PowerPivot

In this module, we will explore PowerPivot and its capabilities, then briefly walk-through the options available and how to configure them.

Lessons

  • Exploring the PowerPivot Interface
  • Loading Data
  • Examining Relationships
  • Configuring Calculated Columns and Calculated Fields
  • Creating PivotTables
  • Understanding DAX
  • Utilizing DAX Functions
  • Using KPIs
  • Working with Hierarchies
  • Managing Perspectives

Lab 1: PowerPivot

  • Introduction to the PowerPivot Interface
  • Importing Data
  • Importing Large Datasets
  • Importing Data with a Query
  • Creating Relationships
  • Creating a Cascading Relationship
  • Hiding Columns, Change Data Type, Sorting, and Filtering
  • Calculated Columns and Calculated Fields
  • KPIs
  • Hierarchies
  • Perspectives

After completing this module, students will be able to:

  • Navigate the PowerPivot interface.
  • Load data.
  • Examine and configure relationships.
  • Configure calculated columns are calculated fields.
  • Create PivotTables.
  • Understand and utilize DAX.
  • Use KPIs.
  • Work with hierarchies.
  • Manage perspectives.

Module 4: Power Query

In this module, we will delve into the benefits and features provided using Power Query. We will examine the options available within the ribbon, as well as explore and demonstrate importing, filtering, and merging data using Power Query.

Lessons

  • Exploring The Power Query Interface
  • Loading Data
  • Managing Modifications
  • Examining Data Cleaning

Lab 1: Power Query

  • Getting External Data
  • Data Filtering
  • Merging Datasets

After completing this module, students will be able to:

  • Navigate the Power Query interface.
  • Load data.
  • Filter data.
  • Merge datasets.
  • Manage modifications.
  • Perform data cleaning.

Module 5: Power Map

In this module we will explore Power Map and its capabilities.

Lessons

  • Exploring The Power Map Interface
  • Using Bing Maps
  • Examining Map Data
  • Reviewing Map Types and Presentation

Lab 1: Power Map

  • View Power Map in Action
  • Geocoding
  • Tours, Scenes, and Layers
  • Adding Time

After completing this module, students will be able to:

  • Navigate the Power Map interface.
  • Use Bing maps.
  • Examine map data.
  • Configure tours, scenes, and layers.
  • Add time to a presentation.

Module 6: Introduction to Power BI

In this module, we will explore the Power BI interface and walk-through the options available.

Lessons

  • Introducing Power BI
  • Explaining Excel and Power BI
  • Examining Data Sources
  • Querying with Natural Language

Lab 1: Introduction to Power BI

  • Introduction to Power BI
  • Data Sources

After completing this module, students will be able to:

  • Navigate the Power BI interface.
  • Utilize data sources.
  • Understand querying with natural language.

Module 7: PowerPivot Introduction

In this module we take a look at the lessons of the module!

Lessons

  • PowerPivot Introduction

Lab : Installing PowerPivot

  • Install PowerPivot for Excel

After completing this module, students will be able to:

  • Install PowerPivot for Excel
  • Be able to navigate the PowerPivot windows
  • Understand the new features of Excel 2010

Module 8: PowerPivot Data Sources

In this module we take a look at the lessons of the module!

Lessons

  • Supported Data Sources

Lab : Loading Data Sources

  • Load Data From SQL Server
  • Load Data From Text Files
  • Load Data via Copy and Paste
  • Load Data via Data Feeds
  • Load Data via Reporting Services

Lab : Cleaning Data

  • Cleaning data in PowerPivot

Lab : Creating Relationships

  • Create Relationship between Data Sources

After completing this module, students will be able to:

  • Import various data sources into PowerPivot
  • Understand how to clean imported data

Module 9: PowerPivot Functions

In this module we take a look at the lessons of the module!

Lessons

  • PowerPivot Expressions

Lab : Create a Time Table

  • Create a Time Table for future labs

Lab : Using DATE Functions

  • Use DATE Function
  • Use DATEVALUE Function
  • Use the DAY, MONTH, YEAR, HOUR, MINUTE, SECOND Functions
  • Use EDATE Function
  • Use EOMONTH Function
  • Use NOW and TODAY Functions
  • Use TIME and TIMEVALUE Functions

Lab : Using TIME INTELLIGENCE Functions

  • Use FIRST* and LAST* Functions
  • Use DATE* Functions
  • Use STARTOF* and ENDOF* Functions
  • Use CLOSING* and OPENING* Functions
  • Use LASTOF* and FIRST* Functions
  • Use PREVIOUS* and NEXT* Functions

Lab : Using STATISTICAL Functions

  • Use AVERAGE* Functions
  • Use COUNT* Functions
  • Use MAX* and MIN* Functions

Lab : Using VALUE Functions

  • Use ALL, ALLEXCEPT and ALLNOBLANKROW
  • Use CALCULATE, CALCULATETABLE
  • Use DISTINCT
  • Use EARLIER and EARLIEST
  • Use FILTER
  • Use REALTED, RELATEDTABLE
  • Use VALUES

Lab : Using LOGICAL and INFORMATION Functions

  • Use Logical Functions (AND, FALSE, IF, IFERROR, NOT, OR, TRUE)
  • Use Information Functions (ISBLANK, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISTEXT)

Lab : Using TEXT Functions

  • Use TEXT Functions

After completing this module, students will be able to:

  • Be able to describe all the new PowerPivot Expressions
  • Use and apply many of the PowerPivot Expressions

Module 10: Building Reports using PowerPivot

In this module we take a look at the lessons of the module!

Lessons

  • PowerPivot Reports

Lab : Creating Reports

  • Use PivotCharts
  • Use Slicers
  • Use Sparklines

After completing this module, students will be able to:

  • Describe the different Report types in PowerPivot
  • Be able to create Slicers and Sparklines for more engaging reports

Module 11: PowerPivot for SharePoint 2010

In this module we take a look at the lessons of the module!

Lessons

  • Reports and Email
  • PowerPivot for SharePoint 2010

Lab : Installing PowerPivot for SharePoint 2010

  • Install PowerPivot for SharePoint 2010
  • Configure PowerPivot for SharePoint

Lab : Configuring PowerPivot For SharePoint 2010

  • Publish to SharePoint 2010
  • Configure PowerPivot for SharePoint 2010

After completing this module, students will be able to:

  • Describe the integration points between PowerPivot and SharePoint 2010
  • Be able to publish and setup workbook data refresh in SharePoint 2010

Prerequisites

  • Working knowledge of Excel
  • General Excel and Report building skills.

Programme scope, delivery arrangements and suitability are confirmed during enquiry.