Skip to main content

Using Excel PivotTables, Power Pivot and Power Query to Analyze Data (OFFERED VIRTUALLY)

Back to Course Schedule
Date(s): Aug 03, 2020 - Aug 04, 2020
Time: 8:15AM - 4:30PM
Registration Fee: $299.00
Cancellation Date: Jul 28, 2020
Location: Online

Course Description

THIS COURSE WILL BE OFFERED VIRTUALLY.

 

Many advanced Excel users such a Mr. Excel have called Power Pivot the Best Thing to Happen to Excel in 20 Years.  This exciting new class will begin with a quick overview or refresher on how to create a Table and a basic PivotTable.  We will then move into more advanced PivotTable topics such as creating Measures, Calculated Fields and Calculated Items. And we will look at PivotTables settings.

 

Next, we will learn how to take multiple tables, add them to Excel’s Data Model, relate them and then create a Power Pivot.  We will look at the Get and Transform feature also called Power Query.  Power Query is often called a self-service Business Intelligence (BI) tool.  In newer versions of Excel, Power Query has been renamed Get & Transform because it allows you to connect to a source, combine and clean up data and then analyze the data.

 

We will learn how to write formulas both in Power Query and the Data Model. Many of these formulas will be like what you have used in Excel.  However, these formulas have a different structure and are often more powerful and work better with large data sets. And a major advantage is the ability to get new data or change data and then then simply refresh your query.  If you write formulas using a VLOOKUPS, INDEX and MATCH or IF statement in Excel, with large data sets you will be amazed at the speed of these newer features.  And if you write macros to clean up data, you might find these new features will work much better than a macro.


Potential CPE Credits: 16.0
Govt Hours: This class meets 16.0 hours of the 24-hour requirement for governmental CPE under Government Auditing Standards (yellow book), in most cases.
Technical Hours: This class meets 16.0 CPE credits of technical training in compliance with Texas Admin. Code Rule 523.102.

Instruction Type: Live
Experience Level: ADVANCED
Category: 02 Auditing

Course Objectives

Upon completion of this course, participants will be able to:

Create a PivotTable and use the more advanced Power Pivot and Get and Transform feature.  The participant will also learn more efficient ways to clean up data and manage it.  Plus, the participant will learn shortcuts and software features that will improve their efficiency when using Excel.

 

Class will begin with a quick overview or refresher on how to create a Table and a basic PivotTable.  We will then move into more advanced PivotTable topics such as creating Measures, Calculated Fields and Calculated Items. And we will look at PivotTables settings.

 

Next, we will learn how to take multiple tables, add them to Excel’s Data Model, relate them and then create a Power Pivot.  We will look at the Get and Transform feature also called Power Query.  Power Query is often called a self-service Business Intelligence (BI) tool.  In newer versions of Excel, Power Query has been renamed Get & Transform because it allows you to connect to a source, combine and clean up data and then analyze the data.

 

We will learn how to write formulas both in Power Query and the Data Model. Many of these formulas will be like what you have used in Excel.  However, these formulas have a different structure and are often more powerful and work better with large data sets. And a major advantage is the ability to get new data or change data and then then simply refresh your query.  If you write formulas using a VLOOKUPS, INDEX and MATCH or IF statement in Excel, with large data sets you will be amazed at the speed of these newer features.  And if you write macros to clean up data, you might find these new features will work much better than a macro.

 

 

CREATING TABLES AND PIVOTTABLES

  • Best Practices for Arranging Data for use with Tables or PivotTables
  • Understand the difference between Filtering data and using Tables
  • Learn the power of Tables
  • PivotTables Basics
  • Learn to Refresh and Modify PivotTables
  • Work with Slicers and Understand How Slicers can help with Dashboards
  • Understand PivotTable Cache
  • Work with PivotTable Timelines

 

 

ADVANCED PIVOTTABLE TOPICS

  • Creating Calculated Fields
  • Creating Calculated Items
  • Office 365 New Data Options
  • Changing the Design and Layout of your Pivot Table

 

 

INTRODUCTION TO POWER PIVOT

  • What is Power Pivot
  • Importing Tables into the Data Model
  • Linking Tables
  • Using the Related() Function
  • Basic Calculations in the Data Model
  • Creating a PivotTable using Multiple Data Sheets

 

 

POWER QUERY aka GET AND TRANSFORM

  • What is Power Query and where is it located in 2013, 2016 and Office 365
  • Types of Data Connections and Power Query Editor Window
  • Review and Change Data Types
  • Loading to the Data Models
  • Data Specific Editing Tools such as Text, Numbers, and Date Tools
  • Filling Data Up and Down
  • Splitting and Combining Columns of Data
  • Adding Conditional Columns
  • Using Formulas such as IF, AND and LOOKUP
  • Basic Understanding of M Functions like Text.PadStart
  • Aggregate vs Don’t Aggregate in a Pivot Table
  • Unpivoting Data
  • Merging Data and working with Joins

 

 

MORE IN-DEPTH LOOK AT POWER PIVOT

  • Creating a Measure using AutoSum
  • Deleting a Measure
  • Working with the New Measures Dialog Box
  • Understanding DAX Syntax
  • DAX Operators
  • DAX Functions such as COUNT ROWS, COUNT DISTRICT AND COUNTA
  • Logical DAX Functions like IF, OR, AND
  • Using the SWITCH Function
  • Working with TEXT Functions

 


Prerequisites

PREREQUISITE: The student should have an Intermediate to Advanced knowledge of Excel.  You will need to understand how to write formulas such as a VLOOKUP and an IF statement. Please read the Course Description and Outline to see if this course is for you.

 

This class will be offered online so participants will need an internet connection, Excel 2016 or Office 365, computer, speaker and microphone.


Instructors

Darla Cloud

Darla Cloud has been teaching computer classes for over 27 years. She has earned the various Microsoft Office certifications acknowledging her expertise in Microsoft products. Darla is also a Certified Public Accountant and a Certified Technical Trainer. In addition to Darla’s years of teaching, she has over seven years of accounting experience. Darla’s accounting experience and love of teaching help make her an excellent trainer. She has spent years learning tips, tricks and shortcuts that she will pass on during her classes.


Additional Information

TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.


Back to Course Schedule