This project demonstrates how to use Power Query and Excel formulas to clean and transform data.
It highlights two different approaches to solving the same problems — automated transformations in Power Query and classic Excel formulas.
The dataset contains customer information, including:
- First Name / Last Name
- Phone number
- Street Address
- Zipcode
- Request Date
Using Power Query and Excel formulas, the exercise covers:
- Combining text fields → Merging first and last names.
- Extracting substrings → Getting area codes and phone segments.
- Cleaning data → Removing unwanted characters (e.g., dashes, parentheses).
- Standardizing addresses → Extracting only street names.
- Fixing zip codes → Preserving leading zeros.
- Creating custom identifiers → Generating short unique IDs.
- Date formatting → Transforming dates into a custom text format like
(19, July, 2014). - Extracting date parts → Isolating the year from dates.
- Power Query transformations
- Split Columns
- Merge Columns
- Extract Text (Start, End, Range)
- Replace Values
- Change Data Types
- Custom Columns with formulas
- Excel Formulas for text manipulation
- Data Cleaning & Preparation
- Automation with Power Query (refresh when new data is added)
- Full Name (Column Q)
=[@[First Name]] & " " & [@[Last Name]] - Phone Number Components (Columns R, S)
=LEFT([@Phone], FIND(")", [@Phone])-1) // Area Code =MID([@Phone], FIND(") ", [@Phone])+2, 8) // Phone Digits - Numeric Phone (Column T)
=SUBSTITUTE(SUBSTITUTE([@Phone], "(", ""), ")", "") - Street Name Extraction (Column U)
=TRIM(RIGHT(SUBSTITUTE([@[Street Address]], " ", REPT(" ", 100)), 100)) - Address-Zipcode Combination (Column V)
=[@[Street Address]] & " -" & [@Zipcode] - Username Generation (Column W)
=LEFT([@[First Name]],3) & LEFT([@[Last Name]],3) & [@Area Code] - Formatted Date (Column O)
="(" & DAY([@[Req Date]]) & "," & TEXT([@[Req Date]],"MMMM") & "," & YEAR([@[Req Date]]) & ")" - Year Extracted (Column P)
=YEAR([@[Req Date]])
