What does clean do in Power Query
Learn what clean does in Power Query, focusing on the Text.Clean function which removes non printable characters from text, improving data quality and reliability during ETL workflows.
Text.Clean is a Power Query function that removes non printable characters from text, making imported data cleaner and easier to work with.
What Text.Clean does in Power Query
Power Query uses the M language for data transformation. Text.Clean is a text function that removes non printable characters from a string, such as control characters and other invisible characters that can sneak into data when parsing from CSV, JSON, or web sources. By cleaning text, you reduce import errors, misalignment during merges, and issues with parsing. The function takes a single text input and returns a new text value with the unwanted characters removed. It also gracefully handles null values by returning null. A typical usage is in a Transform column step, where you transform the values of a text column by applying Text.Clean to every row. This is particularly handy when dealing with data pasted from external sources, copied from logs, or loaded from legacy systems.
In practice, you can apply Text.Clean in Power Query Editor in a few different ways. In a column, add a Custom Column with the expression Text.Clean([ColumnName]). Alternatively, you can Transform a column by selecting the column and choosing Transform > Clean from the UI, depending on your Power Query version. The net effect is to remove characters that do not print in the standard display, which makes downstream steps like matching and splitting more robust. According to Cleaning Tips, applying Text.Clean early in the ETL workflow saves time by preventing downstream quality issues.
-item1
Questions & Answers
What does clean do in Power Query?
In Power Query, Text.Clean removes non printable characters from text strings, helping to produce cleaner data during import and transformation. This reduces hidden characters that can disrupt joins, parsing, and analysis.
Text.Clean removes non printable characters from text in Power Query, making data cleaner and more reliable for matching and parsing.
How is Text.Clean different from Text.Trim?
Text.Clean targets non printable characters, while Text.Trim focuses on removing whitespace from the start and end of a string. Combine both for thorough cleansing before downstream steps.
Text.Clean removes non printable characters; Text.Trim only trims whitespace at edges. Use both for complete cleanup.
Can Text.Clean handle Unicode characters?
Text.Clean removes non printable characters, which can include some control characters across Unicode. Printable Unicode characters remain, so the function preserves meaningful text while removing unwanted control codes.
Text.Clean removes non printable characters across Unicode, keeping characters that are printable.
When should I avoid using Text.Clean?
Avoid Text.Clean if your data relies on specific control characters for encoding or parsing, or if you only need morphological cleanup. Always test with real samples before applying broadly.
Avoid using it if control characters are part of the data protocol or if you only need partial cleaning.
How do I apply Text.Clean in Power Query Editor?
Open Power Query Editor, select the target column, and add a Custom Column with Text.Clean([Column]). You can replace the original column or create a new one for comparison.
In Power Query Editor, add a custom column using Text.Clean on your target column, then decide whether to keep or replace the original.
Are there alternatives to Text.Clean?
Yes. You can use Text.Replace to remove specific characters, Text.Trim for whitespace, and Text.Normalize to standardize Unicode forms. Combine these to tailor cleansing to your data.
Yes, use Text.Replace, Text.Trim, or Text.Normalize to address specific needs beyond non printable characters.
The Essentials
- Use Text.Clean to remove non printable characters
- Combine Text.Clean with Text.Trim for whitespace
- Apply early in imports to prevent errors
- Test with samples to confirm behavior