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.

     

     

     

Mikelytics's avatar
Mikelytics
Resident Rockstar
3 years ago

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.

 

 

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?

    • Mikelytics's avatar
      Mikelytics
      Resident Rockstar
      3 years ago

      Hi Cyrilbrd ,

       

      I am hapy to help but could you please give a clear lsit with input and expected outpout? I am a little bite confused because in my ecxample 2 spaces stay 2 spaces. So please provde a table with examples input and output to cover all your cases.

       

      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 

      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.