Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Grouping table rows and sorting

Hello - i think this is best placed as a power query issue.    I am trying to order a table, but the issue is that the rows need to be grouped also.  An example of the data is as follows:   Tr...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

Hi Anonymous 

 

Does your current table have the Total rows as shown in the example? If so, I can transform it into the following table (Date is sorted ascendingly in each Transaction group and the Total row is on the top of each group).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQrJL0nMAdKGBkDCFEQYGOobGOsbGRgZKcXqQBRFgORAWM8Uu4JIIDYGYiOotAmqdBRUygxTtxOSG0AmmMBsMENVEw6VN0aTjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Transaction = _t, Fund = _t, Value = _t, Variable = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction", type text}, {"Fund", type text}, {"Value", Int64.Type}, {"Variable", type number}, {"Date", type date}}, "en-GB"),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Fund Sort", each if [Fund] = "Total" then 0 else 1),
    #"Grouped Rows" = Table.Group(#"Added Conditional Column", {"Transaction"}, {{"All Data", each _, type table [Transaction=nullable text, Fund=nullable text, Value=nullable number, Variable=nullable number, Date=nullable date, Fund Sort=number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"All Data", each Table.Sort(_, {{"Fund Sort", Order.Ascending}, {"Date", Order.Ascending}})}),
    #"Expanded All Data" = Table.ExpandTableColumn(Custom1, "All Data", {"Fund", "Value", "Variable", "Date", "Fund Sort"}, {"Fund", "Value", "Variable", "Date", "Fund Sort"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All Data",{"Fund Sort"})
in
    #"Removed Columns"

 

However, when I apply it to the report and add columns to a table visual, it doesn't remain this sorting when displaying. You may have a try. 

 

If I didn't understand it correctly, can you please show the expected output format with the example data? 

 

Best Regards,
Community Support Team _ Jing