Forum Discussion

Cyrilbrd's avatar
Cyrilbrd
Helper IV
3 years ago
Solved

Sort text within a single cell

I access some data that is manuaaly encoded by users. the data is often in need of cleaning I am looking into sorting text such as: WT, G, C into C, G, WT G,C into C, G C,G remains C,G Any advi...
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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
    Combine

     

     

    I 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.

     

     

     

Cyrilbrd's avatar
Cyrilbrd
Helper IV
3 years ago

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.

Cyrilbrd's avatar
Cyrilbrd
Helper IV
3 years ago

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?