Replace consecutive appearances of character with one

[igonzalezb]

I have something like this. I want to replace the multiple consecutive appearances of "#" with just one "#"

Data

123###abc3

234#5323##fgd######75

12sg432##

####24##54rc

should become

Data

123#abc3

234#5323#fgd#75

12sg432#

#24#54rc

Any easy way to do it without custom functions?

[AlexisOlson]

I've answered similar questions previously:

https://community.powerbi.com/t5/Power-Query/Consecutive-Delimiters-of-varying-lengths/m-p/2334118
https://community.powerbi.com/t5/Desktop/M-Split-column-with-multiple-spaces-between-fields/m-p/5920...

To use the solution, add a new step that transforms your Data column. For example, you could use Transform > Format > Trim on your Data column. This should produce a step with code like:

= Table.TransformColumns(Source,{{"Data", Text.Trim, type text}})

Now just replace Text.Trim with the transformation you actually want:

= Table.TransformColumns(Source,{{"Data",
    each Text.Combine(List.Select(Text.SplitAny(_, "#"), each _ <> ""), "#"),
    type text}})

Here's a full sample query you can paste into the Advanced Editor on a new blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYkxDsAwCMT+wq1dcoDyGMTQpkr29v9SFLzZjpBGBXA/QyWvEKrB9bS5XhTd6zT+y5RA2Rk0wO0bkrkB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"Data", each Text.Combine(List.Select(Text.SplitAny(_, "#"), each _ <> ""), "#"), type text}})
in
    #"Trimmed Text"

(You might want to rename it something other than #"Trimmed Text". That was the automatically generated name from using the ribbon.)

[Anonymous]

It seems 

Text.Combine(List.Select(Text.SplitAny(_, "#"), each _ <> ""), "#")

works fine, except for leading or trailing occurences of #.

For instance "###a###b###c###", transforms to "a#b#c" in stead of "#a#b#c#"  

[Anonymous]

I would make a custom column with the Add Custom Column named ToReplace with the formula

= Text.Select([Data], "#")

Then you can do

Table.TransformColumns(PriorStepName, {{"Data", each Text.Replace(_, [ToReplace], "#", Replacer.ReplaceText("Data"))}})

--Nate

[serpiva64 · Accepted solution]

Hi, 

to achieve this:

You can:

- add a custom column

- create a new function query (https://www.excelguru.ca/blog/2015/10/08/clean-whitespace-in-powerquery/

- in your original query invoke custom function

- extract text between delimiter

and that's done

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[HotChilli]

Here's a fun solution.

Using right-click on column header, Replace Values, Put ## to be replaced with #.

Then repeat this step.

And then Repeat it again.

That will sort the example.