ONLINE - Excel Power Query and Power Pivot 3/7/2024

Length/CPE Hours

7 hours (1 day)

Class Time

8:30 a.m. – 4:15 p.m. Central Time
15-minute morning and afternoon breaks
45-minute lunch 11:30 to 12:30

Method of Delivery

This class will be held online using GoToMeeting.  Class participants will receive a PDF of the book and will be able to access the class files online.

When you register, be sure to use the email that you would like your GoToMeeting invitation sent to.  The meeting invitation will be sent no later than 1 hour prior to the class. Check your junk mail if you do not receive an invitation, especially if you have a Gmail or Hotmail account.  You do not have to have a GoToMeeting subscription to participate.  

The web browser client will download automatically when you start or join your first GoToMeeting meeting and is also available for manual download here.

https://www.gotomeeting.com/

It is a good idea to download this prior to the online meeting.

PREREQUISITE

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

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
• Appending Multiple Data Sets

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

Cancellation Deadline

The cancellation deadline is 5 business days prior to the class. If you are unable to attend, you may send a substitute. If you are a "no show" or you do not send a substitute you will be charged full price for the class.

Cloud Training Services will consider emergency situations such as a death in the family, illness, and weather, and will make every attempt to work with the person in the event of an emergency.