Text and Date Cleaning
Learn how to clean text values, standardize inconsistent entries, correct date formats, handle invalid dates, and prepare reliable data using Power Query in Power BI.
1. Introduction to Text Cleaning
Text cleaning improves the consistency and quality of text fields. Data imported from different systems may contain extra spaces, inconsistent capitalization, spelling variations, or unwanted characters.
These inconsistencies can affect filtering, grouping, matching, and reporting. For example, "Pune", " pune ", and "PUNE" may need to be standardized when they represent the same city.
Common Text Problems
- Leading and trailing spaces.
- Repeated spaces between words.
- Inconsistent uppercase and lowercase letters.
- Unwanted characters or symbols.
- Different spellings for the same category.
- Empty strings and placeholder values.
Visual 1: Text cleaning helps standardize inconsistent values.
2. Trim, Clean, and Change Case
Power Query provides text transformation options that help standardize text fields. Select a text column and explore the Transform tab or the column context menu.
| Operation | Purpose | Example |
|---|---|---|
| Trim | Removes leading and trailing spaces. | " Pune " becomes "Pune". |
| Clean | Removes non-printable characters. | Removes unwanted control characters. |
| UPPERCASE | Converts text to uppercase. | "Pune" becomes "PUNE". |
| lowercase | Converts text to lowercase. | "POWER BI" becomes "power bi". |
| Capitalize Each Word | Capitalizes the first letter of each word. | "samadhan patil" becomes "Samadhan Patil". |
| Replace Values | Replaces specified text with another value. | "Mum." becomes "Mumbai" when appropriate. |
Visual 2: A simple sequence for standardizing text.
3. Split and Standardize Text
Some columns contain multiple pieces of information in one field. Splitting a column can separate that information into more useful fields.
For example, a full name can be split into first and last names, or a product code can be separated using a delimiter such as a hyphen.
Example: Product Codes
Original value: ELEC-1025-BLK
- Category: ELEC
- Product ID: 1025
- Color: BLK
In Power Query, select the column and choose Split Column. Select the appropriate delimiter and inspect the generated columns.
Visual 3: A product code separated into three fields.
4. Introduction to Date Cleaning
Date values may be imported as text, interpreted using the wrong regional format, or contain invalid values. Standardizing date fields helps with sorting, filtering, time-based analysis, and relationships with calendar tables.
| Source Value | Potential Issue | Recommended Check |
|---|---|---|
| 2026-10-22 | Usually follows year-month-day notation. | Confirm the column is interpreted as a date. |
| 22/10/2026 | Day-first date format. | Use a suitable locale when converting text. |
| 10/22/2026 | Month-first date format. | Confirm the source uses month-first notation. |
| 31/02/2026 | Invalid calendar date. | Investigate or handle the conversion error. |
| Blank | Missing date information. | Determine whether the date is required. |
5. Convert Text into a Date
- Open Power Query Editor.
- Select the date column.
- Review the current data type shown in the column heading.
- Open the data type menu.
- If needed, select Using Locale.
- Choose Date as the data type and select the locale matching the source format.
- Review the converted values and investigate any errors.
Visual 4: A process for converting date text correctly.
6. Extract Date Parts
Once a column has a valid date data type, Power Query can extract useful date components. These can help with grouping, filtering, and preparing data for time-based analysis.
| Operation | Example Date | Result |
|---|---|---|
| Year | 22 October 2026 | 2026 |
| Month | 22 October 2026 | 10 |
| Month Name | 22 October 2026 | October |
| Day | 22 October 2026 | 22 |
| Day of Week | 22 October 2026 | Depends on the selected date and weekday numbering. |
How to Extract Date Parts
- Select a column with the Date data type.
- Open the Add Column or Transform tab.
- Choose Date and select the required component.
- Review the generated column.
- Rename the column if needed.
7. Practical Exercise
Exercise A: Clean Customer Text
- Import a customer table with CustomerName and City.
- Trim leading and trailing spaces.
- Clean unwanted non-printable characters.
- Standardize city names using a consistent naming rule.
- Check the result for unexpected changes.
Exercise B: Clean Order Dates
- Import a table containing OrderID and OrderDate.
- Inspect the original date format.
- Convert text dates using the correct locale.
- Identify invalid and missing dates.
- Extract Year and Month into separate columns.
- Validate the results before loading.
Expected outcome: Standardized text fields and valid date columns ready for reporting.
8. Knowledge Check
Question 1: What does Trim generally do?
A. Removes duplicate rows
B. Removes leading and trailing spaces
C. Changes text into a date
D. Deletes a query
Question 2: When should you use Clean?
A. To remove non-printable characters from text
B. To calculate sales totals
C. To create a relationship
D. To publish a report
Question 3: Why is locale important when converting dates?
A. It changes the report theme
B. It removes all null values
C. It helps interpret regional date formats correctly
D. It creates duplicate rows
Question 4: What should you do with an invalid date?
A. Assume the correct date without checking
B. Investigate the source and handle the error according to data rules
C. Convert every date to zero
D. Delete every row in the table
View Answers
- B — Removes leading and trailing spaces.
- A — Removes non-printable characters from text.
- C — Helps interpret regional date formats correctly.
- B — Investigate and apply the correct data-quality rule.
Summary
In this lesson, you learned how to standardize text using Trim, Clean, case transformations, and Replace Values. You also explored splitting text into separate columns.
You learned how to convert text into dates, use locale settings, handle invalid date values, and extract date components. Always verify transformed values before loading data into Power BI.