Forum Discussion

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:

Transaction	Fund	Value	Variable	Date
A	Total	10	50	01/03/2022
A	X	5	5.5	01/03/2022
A	Y	3	2	01/04/2022
A	Z	2	6	01/03/2022
B	Total	3	45	01/06/2022
B	W	3	3	01/06/2022

The table visual needs to display the Transaction fund Total with each individual fund as rows below it, with all the groups of transaction sorted by the date. A simple way to do this would be to use a matrix and have the transaction as a sub-total, but a couple of the columns are aggregated and so the source data row needs to be use.

In theory, I could create a custom column that applies the Total date to all other funds within the same transaction and then concatenate column with the transaction to create a index identifyer. But i am open to any thoughts on how this can be best approached.

Many thanks in advance

1 Reply

Community Support

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