Effective Data Cleaning & Transformation in Excel.
Course overview.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAININGData is everywhere. With the explosion of data that businesses collect comes new challenges and opportunities. Business leaders at every level need to be data literate and turn complex datasets into actionable insights and make important business decisions. Excel Power Query is the answer to big data that doesn’t fit in ‘normal’ Excel. Use Power Query to connect to hundreds of data sources, simplify data preparation and drive complex analysis. This hands-on class covers the use of Excel built-ins Power Query as tools for cleaning, transforming and combining large amounts of data.
We will show you how, without the need of former data analysis training, you can be analysing data, transforming information into meaningful insights for better decision making. Use Power Query to mold your data in Excel so you can analyse and visualise data and get actionable insights for business decision makings.
Key takeaways.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAININGBy the end of the course, participants will be able to :
- Use Power Query to perform complex data cleaning and transformation
- Automate routine data cleaning and free up time for data analysis
- Combine multiple data sources into one dataset with techniques like merge and append
- Perform text cleaning like trim, clean, add prefix, extract characters, uppercase, lowercase
- Quickly remove or replace errors, duplicates, null values, whitespaces
- Transform & format date and time columns (eg MM/DD/YY to DD/MM/YY quickly)
Who should attend.
This workshop is crucial for analysts, managers, business owners, executives, and anyone responsible for analysing data to gain business insights. This training is highly recommended for:
Excel Users: If you use Excel daily and want to exploit the full power of the Excel to clean and transform data
IT and BI People: Professionals that are in charge of the data warehouse management of their company and need to understand how to provide support to analysts who need to extract and clean data with Power Query
The programme.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAININGSESSION 1Overview
- Overview of training aim, objectives and agenda
- Using Excel for big data analytics
SESSION 2What Is Power Query? >>> Engine To Extract, Transform & Load (ETL) Data
- Using Power Query as an ETL tool
- Power Query M language: Do you need to learn M?
- Importing data from single or multiple sources
- File: Excel, CSV, Folder
- Database (SQL, SAP, Oracle, etc)
- Online services
- Other: Web, OData Feed
- Unstructured vs structured data
- Query Editor ribbon, Navigator pane & Preview grid
- Query settings panel - recording of transformation steps
SESSION 3Data Cleaning & Formatting
- Data types: Number, Date, Text, True/False
- Remove Columns, Remove Other Columns & Choose Columns
- Removing duplicates from a data set
- Replacing values and errors
- Null values: Replace, fill or filter out
- Removing whitespaces and non-printable characters
SESSION 4Data Transformation Techniques
- Converting (selected) columns to rows and vice versa:
- Transpose
- Unpivot columns
- Pivot column What are the differences? When to use them?
- Transforming text columns:
- Format: trim, clean, uppercase, lowercase, add prefix
- Split column & merge columns
- Extract texts before/after delimiter
SESSION 5Data Aggregation & Calculation
- Summarise data with Group By (Sum, Median, Min, Max, etc)
- Custom Columns: Create formulas for computations
- Index Columns (row counters/sort orders)
- Number columns statistical calculations
SESSION 6Combining Data (queries)
- Difference between merge and append
- Merge queries (the V-Lookup in Power Query)
- Append queries (combining tables with the same headers)
- Use cases of merge and append queries
SESSION 7Organising Power Query Automation
- Best practices in organising your data sources
- How to automate your data refresh
- Importing multiple files located in a folder
- Update Power Query for change in location of source data
- Disabling load of redundant queries to enhance performance
SESSION 8Power Query Time Saving Tips
- Copy queries to another Excel workbook or a Power BI file
- Duplicate and reference queries
- Preview or navigate to table objects
- Navigate through complex query dependencies
Your trainer.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAININGAlvin Yong is a UK-registered Fellow Chartered and Certified Accountant (FCCA). He obtained his Master of Science in Professional Accountancy (Merit) in 2017 from the University of London.
To bring.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAININGReviews from our clients.
EFFECTIVE DATA CLEANING & TRANSFORMATION IN EXCEL TRAINING“Content is very good, notes are sufficiently detailed, presentation is clear and pace is good. Alvin entertained questions calmly, clearly, and with sufficient depth. Very knowledgeable and qualified for the training subject.”
“Alvin is an excellent trainer. He led interesting, interactive and focused discussions during the workshop.”
“Alvin was well prepared, knowledgeable and easy to follow. Level of detail was where it needed to be and the documentation was very thorough. The course went quickly which was always a good sign.”
“Fantastic trainer in terms of knowledge and skills. Extremely friendly, helpful and patient.”
“Good understanding and knowledge of the subject. Attentive and responsive to participants' queries. Presented the subject very clearly.”
“The trainer is knowledgeable. He was helpful. He handled the class material well.”
“Very informative. Gave an excellent count of how-to's.”
Related courses.
EXCEL · POWER QUERY · AUTOMATIONReady when your team is.
Public runs across the region and live online — or we scope an internal cohort around your actual reports.
Enquire about in-house training