Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
@Anonymous How can we replace Source as table
@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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.