Skip to main content

Using Excel Functions and Power Query to Clean up and Transform Data

Back to Course Schedule
Date(s): Nov 20, 2024 - Nov 21, 2024
Time: Day 1: 8:00AM - 4:30PM, Day 2: 8:00AM - 12:00PM
Registration Fee: $229.00
Cancellation Date: Nov 13, 2024
Location: SAO COMPUTER TRAINING ROOM
City: Austin, Texas
Parking Info:

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

Often auditors and accountants receive data that needs to be cleaned up before it can be used.  Traditionally users have used Excel formulas to clean up the data. Formulas are still a good option.  But if the task is something you repeat on a regular basis, Power Query might be a better option.  Power Query is a newer tool that connects to various file types like .csv or .txt files, Excel spreadsheets, Access databases, websites and more.  The data can then be transformed and cleaned up using this tool.  The advantage of using Power Query is Excel “remembers” the steps and the next time you need to clean up the data you simply refresh the data.  This can be a big time saver.

This class will first explore how you can use a formula or Excel feature to clean up data.  For example, maybe you need  to split data or combine it.  Maybe it needs to be upper case or formatted.  We will explore formulas and features that you can use to perform these tasks and more.  This is good to know when it is a one-time transformation of data.

Next we will explore Power Query.  We will perform the same tasks we performed with formulas but we will be able to do much more such as unpivot data, remove unnecessary lines and more.
If you are always needing data to be cleaned up, this is the class for you.  This class is designed for intermediate to advanced users.


Potential CPE Credits: 12.0
Technical Hours: This class meets 12.0 CPE credits of technical training in compliance with Texas Admin. Code Rule 523.102.

Instruction Type: Live
Experience Level: INTERMEDIATE
Category: Computer Software and Applications

Course Objectives

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

  • Clean up and transform data that comes from various locations such as text files, database files, Excel and other data sources.


Detailed Course Outline: 

  • 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

Intermediate Excel skills or a class similar to the SAO class called Intermediate/Advanced Excel Features Beneficial for Auditors & Accountants.


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.


Back to Course Schedule