Excel Data Cleansing Straight To The Point
Covers many ways to clean data in Excel, both with and without Power Query
This book helps you decide when to use straight Excel and when to use Power Query.
- 47 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-61547-150-8
This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy. Data cleansing can include the following tasks:
- Deleting unnecessary headers
- Deleting summary rows
- Filling in gaps
- Flattening a report
- Merging and appending data from multiple sources
- Pulling data from source X to complete data in source Y
- Splitting names from addresses
- Identifying and deleting duplicate records
- Converting units of measurement in multiple sources
- Transposing data so it appears in columns rather than rows
- A Data Cleansing Example
- Data Cleansing as a Skill
- The Straight to the Point Ethos
- CORRECTING NAMES: PROPER CASE
COMPARING LISTS: WHAT’S OVER HERE THAT’S NOT OVER THERE?
- Invitations and Responses (Match It All Up!)
- Determining What’s over There That’s Not over Here
- A Word About Strategy
PEELING, PARSING, AND SEGMENTING
- Extracting the First Name (Using Flash Fill)
- Splitting by a Single Delimiter: Separating the City from the Name
- Splitting into Rows: Getting Those People Out of There!
IDENTIFYING DUPLICATE RECORDS: FUZZY MATCHING
- Excel’s Duplicate Remover: The Hazard!
- Reality, Context, and Strategy: Flagging Records for Review Instead of Clearing Duplicates
- MERGING AND APPENDING MULTIPLE WORKBOOKS
FROM USELESS TO USEFUL: FLATTENING A REPORT
- Let’s Flatten Some Stuff!
- FINAL THOUGHTS
About The 'Straight to the Point' Series
Books in this series are designed to thoroughly cover one targeted aspect of Excel.