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. The delimiter is an optional argument and by default set to space ” “. So you can leave it blank if that’s fine for you or enter a different value (like “,” for a comma) if needed.

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.

Edit 28-Jan-2018: While searching the web to see if one of my next blogpost-topics have already been published somewhere else already, I came across Ivan Bond’s blogpost who used this same technique over 2 years ago here: . It’s a very good read and you will also learn how to use a function like this to transform an existing column instead of adding a new one to perform the operation like in my example above, so don’t miss it.

Enjoy & stay queryious 🙂

Comments (14) 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.


  4. Imke,

    A nice post. It got me thinking of how to do the opposite or something like that. I want to look for a character string that is in the following format: 99-99-999-99×9 where 9 is a numeric value and x is a character value. Much like a sin or postal code.
    Do you have any insight on how to do that?



    • Thank you.
      Not sure if I understood your request correctly, but please check this query:
      String = "99-99-999-99x9",
      Test = "asldf asldkfj 12-34-567-89a0 asfdkjö sfadjk",
      fnProfile = (String) => {Text.PositionOfAny(String, {"0".."9"}, Occurrence.All), Text.PositionOfAny(String, {"A".."z"}, Occurrence.All), Text.PositionOfAny(String, {"-"}, Occurrence.All)},
      StringProfile = fnProfile(String),
      Custom1 = Text.Split(Test, " "),
      Custom2 = List.Select(Custom1, each fnProfile(_)=StringProfile)


  5. I think so.
    I will try it in Power BI on the weekend. I plan to use it to a column like this one:

    name change for01-07-009-06W2
    12-04-010-28W1 and 12-05-010-28W1 overlapping when they should not be
    change surface hole location for well 16-23-002-01W2


      • And just to avoid errors when the input is null you could make the first argument nullable text and check for nulls to avoid Text.Combine from speading errors. The version I use is:

        ( String as nullable text, optional TrimChar as nullable text ) as nullable text =>
        trimChar = if TrimChar is null then ” ” else TrimChar as text,
        Split = Text.Split( String, trimChar ) as list,
        Remove = List.RemoveItems( Split, { “” } ) as list,
        Combine = Text.Combine( Remove, trimChar ) as nullable text,
        Result = if String null then Combine else null as nullable text

        Thanks for all the nice articles.


Leave a Reply