Course Information

Please note: This is a 1.5-day course and is offered on January 26 from 8-4:30 and on January 27 from 8-12.

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.

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.

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.

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

No prerequisite required.

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.

To see answers to our Frequently Asked Questions, visit Texas State Auditor's Office - Professional Development FAQs