Forum Discussion
Sorting By Number Column Then By Text Column
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?
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.
5 Replies
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.
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.
- 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"
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.
"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
RemoveColumnsStéphane
