Skip to main content

Excel Power Query - Transform and Cleanup Data for Audits or Analysis Part 1

Back to Course Schedule
Date(s): Feb 16, 2021
Time: 8:00AM - 11:30AM
Registration Fee: $259.00
Cancellation Date: Feb 09, 2021
Location: Online

Course Description

In this online class you will learn how to make connections to data and then modify the data to meet your data analysis needs. Power Query is a powerful data tool that helps Excel users clean up and transform data.  The steps are recorded and then the same query can be used every time you need to clean up a similar file.  Auditors and Accountants often get files from various sources that need to be cleaned up before they can be used.  This class will show you a tool that can often help with that cleanup.  NOTE:  You must have a good understanding of Excel Tables and PivotTables before you take this class.  You must also have either Office 365 or Office 2016.

 


Potential CPE Credits: 3.5

Instruction Type: Live
Experience Level: INTERMEDIATE
Category: 02 Auditing

Course Objectives

Upon completion of this course, students will be able to get data from file types like CSV files and use Power Query to perform tasks such as separate using delimiters, remove columns, change data types, unpivot data, and more.

 

Power Query Basics

·         Define Power Query and Understand What it is

·         Find Power Query in the Ribbon

·         Get Data from a CSV file and transform it

·         Understand the Data Load Options

·         Edit a Query

 

Use Power Query Editing Tools

·         Use the Text Editing Tools

·         Work with the Number Editing Tools

·         Unpivot Data

·         Use Conditional Columns

 

Formulas in Power Query

·         Using IF, ELSE and AND Functions

·         Using JOIN like a VLOOKUP

 

Bonus Material

·         Work with Modulo


Prerequisites

The prerequisites is the class “How Auditors and Accounts Can Use Tables and PivotTables (Online class)” or equivalent working knowledge.

 


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.


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.

The course coordinator will contact you with parking information. Handicapped parking is free at the meters around the downtown area.

Vending machines with Coca-Cola products and various snack items are available. There is also a refrigerator and microwave in our coffee bar area. Feel free to bring in your own drinks and food if you prefer.

You might want to bring a light sweater or jacket, as room temperatures vary.

To see answers to our Frequently Asked Questions, visit http://www.sao.texas.gov/training/faq.html.


Back to Course Schedule