Course Information
Using Excel PivotTables, Power Pivot and Power Query to Analyze Data
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will Email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
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.
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.
Course Outline:
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
Should have an Intermediate to Advanced knowledge of Excel. You'll need to know VLOOKUP and IF statement.
Instructors
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.
If you are making travel plans to come to Austin, we recommend making "refundable" air and hotel reservations or waiting until 14 days before the class to actually book your reservations. Courses are occasionally canceled or rescheduled due to low enrollment. We determine whether a course has enough participants 16 days prior to the course date. If we cancel or reschedule, we will email the participant and his or her billing contact no later than 14 days before the original class date.
Vending machines with Coca-Cola products and various snack items are available. There is also a refrigerator and microwave in our coffee bar area. Feel free to bring in your own drinks and food if you prefer.
You might want to bring a light sweater or jacket, as room temperatures vary.
To see answers to our Frequently Asked Questions, visit http://www.sao.texas.gov/training/faq.html