How to Trim and Remove Spaces from Multiple Columns in Power Query

Efficient data cleaning is a crucial step in any data preparation process, and Power Query makes it easy to handle common tasks like trimming and removing unnecessary spaces with functions that you can apply across multiple columns and queries at once. By creating and invoking a function, you can quickly trim and remove spaces from all the columns & tables you need, saving time and effort. In this blog, we’ll show you how to use Power Query functions to streamline your data-cleaning process.

The power query we are going to use to trim text in columns is –

(text as text, optional char_to_trim as text) =>
let
char = if char_to_trim = null then ” ” else char_to_trim,
split = Text.Split(text, char),
removeblanks = List.Select(split, each _ <> “”),
result=Text.Combine(removeblanks, char)
in
result

This Power Query function takes text as input and removes extra spaces or a specified character from a text string. It splits the text into parts, filters out empty strings, and recombines the cleaned parts using the specified character. If no character is provided, it defaults to removing spaces.

The power query we are going to use to remove spaces from the text is –

(InputTxt as text) =>

let

Clendata = Text.Combine(
List.Select(
Text.Split(Text.Trim(InputTxt),” “),
each _ <> “”
),
“”
)

in

Clendata

The Power Query function removes all spaces from a given text string. It trims the input, splits it by spaces, filters out blanks, and then combines the parts into a single string. The result is a clean, space-free text, ideal for standardized data preparation.

Now, we have our power query function ready, we can use this function across multiple columns or dataset. To do so, go to Add Column > Invoke Custom Function > Your Power Query Function.

To conclude, Cleaning and transforming data in Power Query become much easier and more efficient with the use of custom functions. Whether you need to remove spaces, clean multiple columns, or standardize text, these functions save time and ensure consistency across your dataset. By applying these techniques, you can handle large, messy datasets with ease, making your data ready for analysis or reporting. Start implementing these simple yet powerful methods today to streamline your data preparation process!

We hope you found this blog useful, and if you would like to discuss anything, you can reach out to us at transform@cloudfonts.com.