This 4-hour workshop offers a beginner-level introduction to Excel’s Power Query, focusing on its application in auditing and data cleaning within general ledgers. Participants will learn how to use Power Query to streamline the management of unwieldy client data, effectively clean and organize data, and enhance their overall efficiency.
Introduction (30 minutes)
1.1. Course overview and objectives
1.2. Importance of data cleaning in auditing
1.3. Introduction to Power Query and its benefits for auditors
Getting Started with Power Query (45 minutes)
2.1. Understanding Power Query's interface and features
2.2. Importing data from various sources (Excel, CSV, databases)
2.3. Connecting Power Query to general ledger systems
Data Cleaning and Transformation Techniques (90 minutes)
3.1. Filtering and sorting data
3.2. Removing duplicates and irrelevant data
3.3. Splitting and merging columns
3.4. Data type conversions and formatting
3.5. Conditional transformations and error handling
3.6. Combining and appending data from multiple sources
Power Query Best Practices for Auditors (45 minutes)
4.1. Structuring queries for easy auditing
4.2. Documenting and commenting query steps
4.3. Maintaining data integrity and security
4.4. Automating repetitive tasks
Hands-On Exercise: Cleaning a Sample General Ledger Dataset (30 minutes)
5.1. Applying the techniques learned to clean a sample dataset
5.2. Reviewing and discussing the results
Conclusion and Next Steps (15 minutes)
6.1. Recap of the key takeaways
6.2. Additional resources and learning materials
6.3. Q&A and closing remarks
The course is designed for auditors with little or no experience with Power Query and includes hands-on exercises.
