OUTLINE

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: ADVANCED

1.     The Excel Table

What is a flat file?

Overview of the Excel Table

Creating the Table

Filtering & sorting the Table

The Table Ribbon

Naming & resizing the Table

Table styles

Inserting slicers

Inserting formulas in the Table

Best practices on maintaining good data

2.    Introduction to the Pivot Table

Creating the Pivot Table

Populating Rows, Values, Columns & Filters

Formatting values in the Pivot Table

Pivot Table subtotals & grand totals

Pivot Table Report Layout & Styles

Components in Pivot Table Options

Renaming the Pivot Table

Drilling down into data

3.    Filtering & Sorting

Date filters, Label filters & Value filters

Sorting items in Pivot Table in an ascending or descending order

Slicers & Timeline

Filter Connections

4.    Calculated Fields & Items

How to insert a Calculated Field

Calculated Field behavior

5.    Pivot Charts

What is a Pivot Chart?

Creating a Pivot Chart

Pivot Chart rules and design

Managing the underlying data, adding layers and altering structure

Dynamic Charts

6.    Retrieving & summarizing data from the Pivot Table

Introduction to the GETPIVOTDATA Function

Using GETPIVOTDATA with the Data Validation List

7.    Managing Changes in Your Source Data

Dynamic Named Ranges

Dealing with zeroes

8.    Business Intelligence overview

What is BI

The OLAP & relational databases

Introducing the Data Model

ETL with Power Query

Creating a Data Model with Power Pivot

Summarizing and analyzing data from data models

Exploring functionalities of the OLAP Pivot Table with CUBEVALUE & CUBEMEMBER

Integration of the Pivot Table with Data Models and Power BI

  9.    Power BI introduction

Introduction to the interphase in Power BI

Introduction to functionalities and components of Power BI Desktop and Power BI Service.

10.        Data Modelling using Power BI

ETL with Power BI

Data Modelling with Power BI

Publishing data into Power BI Service

Analyzing data between Power BI and Excel.