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:

Challenge

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.

    Reply

Leave a Reply