ONLINE - Clean up Excel Data with Formulas & Power Query 12/12-13/2024

Length/CPE Hours

12 hours (2 day)

Class Time

Day 1 - 8:30 a.m. – 4:00 p.m. Central Time
Day 2 - 8:30 a.m. – 3:00 p.m. Central Time

One-hour break for lunch and a 15-minute morning and afternoon break.

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.

Course Description

Excel users receive data from many sources and often the data needs to be cleaned up before it can be used. This class will teach formulas that are often used when cleaning up data. A large portion of this class will be spent on an awesome tool called Power Query. Power Query is and ETL tool which allows the user to extract data from sources like CSV and text files, Excel Tables, Databases and more. Then Power Query can transform the data and load it.

And Power Query “remembers” the steps so if you use another file with the same layout all you need to do is refresh and the work is done. Most Power Query steps are performed using the ribbon verses using formulas. However, we will look at formulas you can use in Power Query if you want to work with more complicated tasks.

This class is designed for the intermediate to advanced Excel user who wants to expand their knowledge of Excel. In all Excel classes, we focus on learning shortcuts, keystrokes and the most efficient way to accomplish a task.

In the class, you will learn:

COMMON FUNCTIONS AND FEATURES IN EXCEL TO CLEAN UP DATA

• Use Flash Fill to combine, split or format data quickly without formulas
• Join Text using CONCAT and TEXTJOIN and shortcuts
• Understand Copy and Paste Value
• Split Text using Text-to-columns
• Brief look at LEFT, RIGHT, MID and LEN functions and how they can be used in data clean up.
• Change the case of text using UPPER, LOWER, and PROPER
• Use the TRIM function to remove extra spaces
• Identify Duplicates with Conditional Formatting
• Remove Duplicates
• Use the TEXT function, especially when you lose leading zeros
• Transpose data using the TRANSPOSE function or copy paste transpose

USE POWER QUERY TO CLEAN UP AND TRANSFORM DATA

• What is Power Query
• Types of Data Connections and Power Query Editor Window
• Review and Change Data Types
• 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
• UnPivoting Data
• Merging Data and working with Joins
• Combine text files from a Folder
• Editing some PDF files
• Importing data from a website
• Importing data from a Picture
• Importing data from the Snipping Tool
• Loading to the Data Model
• A Brief Look at Power Pivot – Power Pivot is a separate class but Power Query can load data to the Power Pivot Data model

Prerequisites

Students must have a beginner to intermediate level knowledge of Microsoft Excel.

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.