Course Information

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.

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

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