Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi @untapjoseph
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.