Sorting By Number Column Then By Text Column

[MagdoulinShams]

I have a column with amounts, I need to sort descending to get the highest at the top, however, there is a catch here, there is another column which is for the IDs, it got texts, so, I need to sort the highest, yes, but to sort then by the ID, so, if I have the below sample:

I need to end up having this:

However, I always end having the data sorting by the amount column solely. I have tried many work around to neuter the numbers column by converting to text and using Text.PadStart function, but nothing works, I cannot figure a logic to apply to reach what I need, any suggestions?

[slorin]

Hi @MagdoulinShams 

"I need to sort descending to get the highest at the top, however, there is a catch here, there is another column which is for the IDs, it got texts, so, I need to sort the highest, yes, but to sort then by the ID"

Group by ID A01111, A00000, ... with max Amount and data

Sort by Max

Expand

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQAAV0DJR0lIyBDKVYHJGYIBGAxYzQxQ6CYIVgsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Amount", Int64.Type}}),
TextBefore = Table.AddColumn(#"Changed Type", "ID1", each Text.BeforeDelimiter([ID], "-"), type text),
Group = Table.Group(TextBefore, {"ID1"}, {{"Max", each List.Max([Amount]), type nullable number}, {"Data", each _, type table [ID=nullable text, Amount=nullable number, ID1=text]}}),
Sort = Table.Sort(Group,{{"Max", Order.Descending}}),
Expand = Table.ExpandTableColumn(Sort, "Data", {"ID", "Amount"}, {"ID", "Amount"}),
RemoveColumns = Table.RemoveColumns(Expand,{"ID1", "Max"})
in
RemoveColumns

Stéphane 

[Anonymous · Accepted solution]

Hi @MagdoulinShams 

You can create a blank query and put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQAAV0DJR0lIyBDKVYHJGYIBGAxYzQxQ6CYIVgsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Amount", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ID", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"ID.1", "ID.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", type text}, {"ID.2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"ID.1", Order.Descending}, {"Amount", Order.Descending}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"ID.2", type text}}, "en-US"),{"ID.1", "ID.2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

I don't understand your logic. When you sort by 2+ columns, it always sorts by the first column. The second column sort only comes into play when the first column has dupes, otherwise it is irrelevant.

[MagdoulinShams]

I understand this, and that is the issue, the default logic blocks what I need to reach.

What I need to sort by amounts, till we reach the same category, so, all the sub-categories come under each other starting from this point till we are moved to the next category so we sort by the amounts one more time and so on.

[ronrsnfld]

• Add a custom column with just the main Category by splitting on the hyphen
• Sort by Category / Descending and then by Amount / Descending
• Delete the custom column

let

//Change next lines to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Amount", Int64.Type}}),

//Add a custom column containing just the "main" category
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", each Text.Split([ID],"-"){0}),

//Then sort by Category and Amount
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Category", Order.Descending}, {"Amount", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Category"})
in
    #"Removed Columns"