Sort text within a single cell

[Cyrilbrd]

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 advise?
This would be added as a new colum into the editor if possible.

[Mikelytics]

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.

[Cyrilbrd]

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

[Mikelytics]

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]

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]

@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]

@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 · Accepted solution]

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]

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.

[amitchandak]

@Cyrilbrd , Are these row values of column value, You can create a new column in power Query like

If [Column] ="W" then 0

else If [Column] ="C" then 1

else 3

Add additional else as per need

Mark that new column as sort column

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c

[Cyrilbrd]

Thanks for the proposed solution but this would not work, as several databases exist and new types may be added as business grows.
The proposal from @Mikelytics works well with the present model.