EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

Data Visualization with Excel

Course outline

2 days
Discuss this programme →

Duration

2 days

OVERVIEW

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. The course also includes creating visualizations, the Power BI Service, and the Power BI Mobile App.

AUDIENCE PROFILE

The course is targeted towards business analysts, business intelligence developers, and reporting professionals and who need to analyse data utilizing Power BI. The secondary audiences for this course are technically proficient business users.

PRE-REQUISITE

Before attending this course, students must have:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Basic knowledge of data warehouse schema topology (including star and snowflake schemas).
  • Some exposure to basic programming concepts (such as looping and branching).
  • An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
  • Familiarity with Microsoft Office applications – particularly Excel.

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.
  • Perform Power BI desktop data transformation.
  • Describe Power BI desktop modelling.
  • Create a Power BI desktop visualization.
  • Implement the Power BI service.
  • Describe how to connect to Excel data.
  • Describe how to collaborate with Power BI data.
  • Connect directly to data stores.
  • Describe the Power BI developer API.
  • Describe the Power BI mobile app.

Module 1: Course Overview

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

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: Introducing Power BI

Self-Service Business Intelligence (BI) has rapidly grown in popularity because of its ability to empower users to generate reports, process data, perform analysis, and more—all without having to depend on a report developer. The Self-Service BI trend is driven by Microsoft's commitment to improving Excel and Power BI, both products having seen many enhancements over recent years. However, despite Microsoft enabling deeper data analysis with the four power tools added to Excel— Power Pivot, Power View, Power Query, and Power Map—they are not fully integrated into the Excel interface. Instead, they exist in separate windows. Add to this the complexity of publishing to SharePoint to share reports with colleagues, and it all becomes a time-consuming effort.

Lessons

  • Power BI
  • The Power BI Service

Lab : Creating a Power BI Dashboard

  • Connecting to Power BI Data
  • Create a Power BI Dashboard
  • After completing this module, students will be able to:
  • Develop reports using the Power BI Desktop app.
  • Use report items to create dashboards on the Power BI portal.
  • Understand the components of the Power BI service, including licensing and tenant management.

Module 7: Power BI Data

Power BI offers a straightforward approach to report creation, and the ability to create and share dashboards without dependency on a report developer, or the need for Microsoft SharePoint. Microsoft Excel has long been the tool of choice for data analysts who work in a self-service style. However, Excel does not offer a quick and easy way to share reports without the use of either SharePoint, or the creation of multiple copies of spreadsheets that quickly become out of date, or exist outside source control.

Lessons

  • Using Excel as a Data Source for Power BI
  • The Power BI Data Model
  • Using Databases as a Data Source for Power BI
  • The Power BI Service

Lab : Importing Data into Power BI

  • Importing Excel files into Power BI
  • Viewing Reports from Excel Files

After completing this module, students will be able to:

  • Describe the data model and know how to optimize your data within the model.
  • Connect to Excel files and import data.
  • Use on-premises & cloud SQL Server databases as data sources, with the R script data connector.
  • Take advantage of the features of the Power BI service.
  • Use Q&A to ask questions in natural query language and create apps.

Module 8: Shaping and Combining Data

Power BI Desktop offers a self-service solution for creating visual, interactive reports and dashboards. Users can connect to a wide variety of data sources, combining data from on-premises databases, Software as a Solution (SaaS) providers, cloud-based services, and local files such as Microsoft Excel, into one report. The beauty of Power BI reports and dashboards is the ability to rapidly build reports to present this data, so it is instantly readable—with clusters, outliers, and patterns in data visually brought to light. To achieve this, each report must have a dataset comprising tables and columns that are ready to add straight into visualizations. Data must be formatted for relevant currencies, numbers should have correct decimal places, additional columns and measures might be required, and data may have to be combined from multiple tables. With Power BI Desktop, you can do all of this, with powerful, built-in tools for shaping your data. This module introduces the tools that are available for preparing your data and transforming it into a form ready for reporting.

Lessons

  • Power BI Desktop Queries
  • Shaping Data
  • Combining Data

Lab : Shaping and Combining Data

  • Shape Power BI Data
  • Combine Power BI Data

After completing this module, students will be able to:

  • Perform a range of query editing tasks in Power BI.
  • Shape data, using formatting and transformations.
  • Combine data together from tables in your dataset.

Module 9: Modelling Data

Microsoft Power BI is making its mark in the self-service BI world—because it can quickly create visually stunning, interactive reports, and dashboards. Power BI provides a straightforward way to combine data from a wide range

of sources into a single dataset, and then work with that data to create cohesive reports. This module goes behind the scenes of the visualizations and explores the techniques and features on offer to shape and enhance your data. With automatic relationship creation, a vast library of DAX functions, and the ability to add calculated columns, tables, and measures quickly, you will see how Power BI creates attractive reports, while helping you find hidden insights into data.

Lessons

  • Relationships
  • DAX Queries
  • Calculations and Measures Lab : Modeling Data
  • Create Relationships
  • Calculations

After completing this module, students will be able to:

  • Describe relationships between data tables.
  • Understand the DAX syntax and use DAX functions to enhance your dataset.
  • Create calculated columns, calculated tables, and measures.

Module 10: Interactive Data Visualizations

Self-service business intelligence (BI) is becoming increasingly popular in organizations. This approach enables business users to access corporate data and create and share reports and key performance indicators (KPIs) without dependency on a dedicated report developer. Business users can use the Microsoft Power BI suite of tools to connect to a wide variety of data sources. These include the main industry-standard databases, Microsoft cloud-based services—Microsoft Azure SQL Database, Azure Data Lake, and Azure Machine Learning—alongside Microsoft Excel and other files, and software as a service (SaaS) provider such as Microsoft Bing, Facebook, and Mail Chimp. The combination of flexibility and the ability to create visually stunning, interactive dashboards quickly makes Power BI an obvious choice for any organization that needs to provide its users with a self- service BI solution.

Lessons

  • Creating Power BI Reports
  • Managing a Power BI Solution Lab : Creating a Power BI Report
  • Connecting to Power BI Data
  • Building Power BI Reports
  • Creating a Power BI Dashboard

After completing this module, students will be able to:

  • Use Power BI Desktop to create interactive data visualizations.
  • Manage a Power BI solution.

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