Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data cleansing - Conditonal replacement by list of special characters

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

Interesting Links
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago
    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

6 Replies