Business Intelligence with Microsoft Excel

Business Intelligence with Microsoft Excel Business Intelligence with Microsoft Excel
By TED Integrated
Course Title:
Introduction to Power Business Intelligence with Excel 2010
Training Category:
Information Technology
Target Audience:
Excel Power Users, data analysts, business analysts, managers, MIS professionals and BI Consultants / BI Developers.
Duration:
1 Day

Course Introduction ›

PowerPivot is an add-on for Microsoft Excel 2010 that allows you to import huge amount of data from various data sources into a single Excel workbook. You can then create relationships between heterogeneous data, create calculated columns and measures using formulas. You can also build PivotTables and Pivot Charts, and then analyze the data further so that you can make faster business decisions without much assistance from your IT support.

One of the main components of Power Business Intelligence is Power Query. Its functionality is for to perform data transfer all by yourself. It connects to wide range of data sources. Data sources such as SQL Server, Oracle, Text files, Big data and Hadoop, online searches and OData feeds, Cloud storage, Facebook, SharePoint, and many other types of data storage can be used as the data source for Power Query. Power Query extracts the data from sources and perform data transformations such as splitting columns, data conversions, lookups, merge, append, and many other data transformation activities.

This hands-on course demonstrates how to take advantage of self-service business intelligence using Microsoft PowerPivot & Power Query for Excel.

Course Objectives ›

During the training, you will use PowerPivot to:

  • Import external data from multiple sources.
    • Client database
    • Excel tables
    • CSV files
  • Handling massive data from multiple sheets.
  • Create relationships between multiple tables.
  • How to create a user friendly models
  • Perform simple calculations in Power Pivot
    • Calculated columns.
    • Calculated fields.
  • Create PivotTables.

You will also use Power Query to automate the process to:

  • Combine data from multiple sources into a single source.
  • Merging data without the need of formulas such as VLOOKUP, INDEX & MACTCH
  • Performing data aggregation
  • Getting data from multiple workbooks and multiple worksheets.
  • Transform data (transform columns into attribute-value pairs where columns become rows)

Prerequisites ›

Delegates should be proficient users of Excel and experienced in creating Excel PivotTables & PivotCharts and also be familiar with Functions & Formulas.

Course Outline ›

DAY 1

Introduction to PowerPivot

This module explains what PowerPivot is and how you can download and install PowerPivot on your computer.

  • What is PowerPivot
  • Limitation of the normal Pivot Table
  • The Power Pivot Interface

Handling Data in Power Pivot

This module explains how to bring your data into Excel whether it's an external source, text files, Excel data and other sources. It also shows you how to build relationships between tables which is vital to data analysis.

  • Importing Data
  • Creating Linked Tables
  • Managing Relationships
  • Creating Your Power Pivot Table

Power Pivot at Work

This module explains how to use create and format your reports as creating reports from Power Pivot may differ from a normal pivot table. It also touches on making your data model more user friendly for end users.

  • Creating and customizing reports that are user friendly
  • Formatting your Numbers
  • Customizing your data model
  • Performing calculations with calculated columns
  • Performing calculations with calculated fields
  • Working with dates

Introduction to Power Query

This module explains what Power Query is and how you can download and install the power query on your computer.

  • Downloading Power Query
  • Install and Enable the Power Query Tab

Importing Data Files

This module explains how to bring your data into Excel whether it's an external source, text files, Excel data and other sources.

  • Appending Data From Multiple Formats
  • Merging Data Without Formulas
  • Getting Data Files From A Folder
  • Getting Data From Multiple Workbooks
  • Getting Data From Multiple Worksheets

Transforming Data

Crosstab tables, or two-dimensional pivot tables are an excellent tool to present data. However, they are not suitable for data analysis. In Power Query, you will be able to transform selected columns into attribute-value pairs where columns become rows for data analysis.

Contact us now ›

Related Courses ›

Published by: ,