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