Forum Discussion
Sort text within a single cell
Hi Cyrilbrd
The following code ahieves this
let Replace1 = Replacer.ReplaceText([Column]," "," "), Replace2 = Replacer.ReplaceText(Replace1," "," "), Split = Text.Split(Replace2, " "), Trim = List.Transform(Split, each Text.Trim(_)), Sort = List.Sort(Trim), Combine = Text.Trim(Text.Combine(Sort," ")) in CombineI assume now it creates a conflict wwith the other requirement. If yes, please now provide a list of different combination with input column and oputput column. Otherwise it will be endless back and forth 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi Cyrilbrd
I think I have something for POwer Query:
This is my base data
Then I add a custom column with the followin function
Please replace put into [Column] the name of your column like [MyColumnName]
let
Split = Text.Split([Column], ","),
Trim = List.Transform(Split, each Text.Trim(_)),
Sort = List.Sort(Trim),
Combine = Text.Combine(Sort,", ")
in
Combine
Output
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Mikelytics Good morning and thank you for the proposed solution.
It essentially works as required.
Adjusted delimiter and fields as required.
Question:
How would I get rid ot trailing space?
Example C, G versus C, G_
Where _ would represent a trailing space accidentally encoded by the user.
I used "Replace Values" to get rid of some unwanted CHAR and others, but that trailing(s) space is an issue I have not solved yet.
Hi Cyrilbrd
Great that it works as expected. WHat I do not get is why you can not use replace values with "_".
Maybe I do not understand the problem properly 😕
dataset:
aftert replacing values ("_" -> "")
after using the function
Can you maybe specify with an example what you mean?
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.Sorry I was not clear Mikelytics .
Let me give you an example from the DB
One user encode the follow TRI TRIA with 3 spaces in between the two codes
Once sorted with your solution I would get a TRI TRIA with 2 spaces, then first code then one space and second code.I get 2 to 3 spaces (depending) on the user.
disregard the _, I just used it to 'represent a space' in this thread.
I can use TRIM to remove any trailing space, but I am not sure how to effectively clean the code so only one space exist in between codes.Mikelytics just an update.
This is what I tried:
Replace Values= Table.ReplaceValue(Source,",","#(00A0)",Replacer.ReplaceText,{"Column"})
followed by
= Table.TransformColumns(#"Replaced Value",{{"Column", Text.Trim, type text}})
And it seem to be working so far.
Do you think this would suffice or do you have a better solution for me to test?