Course Information
Using Excel Functions and Power Query to Clean up and Transform 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
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.
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.
Outline:
• 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
This is an intermediate to advanced class. Users should have taken an Excel Level 1 course, or equivalent working knowledge of the software.
YB 4.23 (P)
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.
To see answers to our Frequently Asked Questions, visit Texas State Auditor's Office - Professional Development FAQs