Remove repeating characters from a string in Power BI and Power Query

Repeating spaces often cause problems when cleaning up your data. My new function “Text.RemoveRepeatingCharacters” can come to the rescue here.

Imagine you have a table like this:


To further work with this data, it would often be best if there was just one space between the words and not many.

The following function will do this for you:

Function Text.RemoveRepeatingCharacters

How to use

It takes 2 arguments: The Text/string and the Delimiter. So for our example you have to enter a space, but you can use any sign there for other use cases as well.

How it works

It splits the text up into a list using the delimiter from the 2nd parameter (4: TextToList). Where one delimiter directly follows another, the element in the list will be empty. The next step (5: FilterList) then filters the list and removes these empty fields. In the last step (6: Result) the remaining (non-empty) fields will be reassembled, using the delimiter again. That way, just one delimiter will be left.

Enjoy & stay queryious 🙂

Comments (7) Write a comment

  1. Pingback: Removing Repeating Characters In Power Query – Curated SQL

  2. Pingback: Power Query and a new Power BI Desktop update | Guy in a Cube

  3. Imke – rather than create a custom function — my approach has been to nest the three functions into a single line of code ie: Text.Combine ( List.Select( Text.Split (….. ) ) ) — enabling the query to execute seamless – absent the invocation requirement associated with a custom function.

    Thanks – Drewbbc.


Leave a Reply