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

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?

Cyrilbrd's avatar
Cyrilbrd
Helper IV
3 years ago

Mikelytics 

No space before first code, a maximum of one space in between codes, no space after last code.

So from:
    G    C       A    

to

A C G

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    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.