CODEWITHSUNDEEP
sortingpowerquerym
TXP
TXP

Reputation: 59

How to sort a comma separated string with a specific value on first position?

Lets say I have an unsorted string like "Apples,Bananas,Pineapples,Apricots" in my query. I want to sort that list and specificly have "Bananas" first in the list if they occur and the rest sorted ascending.

Example:

[BASKET] | [CONTENT]                  | [SORTED]
John     | Apples,Apricots,Bananas    | Bananas,Apples,Apricots
Melissa  | Pineapples,Bananas         | Bananas,Pineapples
Tom      | Pineapples,Apricots,Apples | Apples,Apricots,Pineapples

How can I accomplish this with Power Query?

Upvotes: 2

Views: 475

Answers (1)

horseyride
horseyride

Reputation: 21318

Cheap version (a) replace Banana with something that will sort first in strict alpha sort (b) Sort in new column (c) Fix Banana (d) remove extra column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Replaced Value" = Table.ReplaceValue(Source,"Bananas","111Bananas",Replacer.ReplaceText,{"Items"}),
MySort = (x) => Text.Combine(List.Sort(Text.Split(x, ",")), ","),
Sorted = Table.AddColumn(#"Replaced Value", "Sorted", each MySort([Items])),
#"Replaced Value1" = Table.ReplaceValue(Sorted,"111Bananas","Bananas",Replacer.ReplaceText,{"Sorted"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced Value1",{"Items"})
in #"Removed Columns"

Upvotes: 2

Related Questions