Excel BI Challenge 311 - Sort Text Numbers - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    SplitToRows = Table.ExpandListColumn(Table.TransformColumns(AddIndex, {{"Number", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Number"),
    AddTextToNum = Table.AddColumn(SplitToRows, "Custom", each if [Number] = "Four" then 4 else if [Number] = "Six" then 6 else if [Number] = "Two" then 2 else if [Number] = "One" then 1 else if [Number] = "Seven" then 7 else if [Number] = "Five" then 5 else if [Number] = "Zero" then 0 else if [Number] = "Nine" then 9 else if [Number] = "Three" then 3 else if [Number] = "Eight" then 8 else null, type number),
    Group = Table.Group(AddTextToNum, {"Index"}, {{"All", each Table.Sort( _, {"Custom", Order.Descending}), type table [Number=nullable text, Index=number, Custom=number]}}),
    RemCol = Table.TransformColumns( Table.RemoveColumns(Group,{"Index"}), {"All", each Table.SelectColumns(_, "Number")}),
    TextCombine = Table.AddColumn(RemCol, "Custom", each Text.Combine( Table.ToList( [All]), "")),
    RemAllCol = Table.RenameColumns( Table.RemoveColumns(TextCombine,{"All"}), {"Custom", "Number"})
in
    RemAllCol