Data cleansing - Conditonal replacement by list of special characters

[chrispybacon]

Hello everyone, 

currently I am facing a problem in PQ that I am not sure how to solve. 

Basically I need to clean a free text field that is created by the users of my application that I am getting the data from. 

So assuming I have the following string: "aaa-bb bb-ccc//dd?eee", I would like to "have aaa_bb_bb_ccc__dd_eee"

Since this a free text field the number of special characters they use as delimiters can be really large. So is there a way to have a list with all special characters and then check in the string of each record if it appears, and if any of the special charachters appear to replace them by "_". 

I would like to avoid having all these additional Replace transformations steps. 

I need that step because later I need to do a categorization where I do something like 

each if List.ContainsAny(Text.Split([col], "_"), checklist1) = true then "output" else if [and so on]

I was googling but couldn't find any useful source for the above scenario. Any link to resource or idea is appreciated :). 

Cheers,

Christian

[spg_vizcube]

Hi @chrispybacon 

Probably this would help 

https://community.powerbi.com/t5/Desktop/Power-Query-Replace-multiple-substrings-in-one-column/td-p/...

Appreciate your Kudos!

If I solve your problem, please accept this as a Solution

Add me on Linked In , Visit my blog vizcube.biz
Subscribe to my youtube channel 

[Anonymous · Accepted solution]

let
    Source = "aaa-bb bb-ccc//dd?eee",

    special=Splitter.SplitTextByRepeatedLengths(1)(Source),

    res=Text.Combine( List.Transform(special, each if List.Contains({"a".."z"}, _) then _ else Text.Replace(_, _, "_")))


in
res

[chrispybacon]

Hi @Anonymous, 

that approach worked nicely! That also helped me to learn something about the use of "_". Also sorry for the late reply I was on holiday ;). 

Best regards,

Christian

[spg_vizcube]

@Anonymous How can we replace Source as table

[Anonymous]

@spg_vizcubeif you intend to apply the transformations to the values of some column of a table, this can be done by defining a function that takes as input a string and gives as output a transformed string and using it with the add custom column function or somethink like transform columns.

what is exactly your case?

[Anonymous]

yet another way to change the set of "not standard" chars to a given default char:

let
str_in="aaa-bb bb-ccc//dd?eee",
str_out=Text.Combine( List.Transform(Splitter.SplitTextByRepeatedLengths(1)(str_in), each Record.FieldOrDefault(Record.FromList({"a".."z"},{"a".."z"}),_ ,"_") ))
in
str_out