Forum Discussion
Aggregating data from multiple columns in one row (dynamic sorting)
Hallo,
I have a table with some IDs and Events that are associated with that ID. Each ID has a date (e.g. Example Table). I would like to get for each ID one row with Events but sorted according to Date/Time Column.
I Sorted that table by Column 1, and Column4,. After that I grouped the Data (ID column) and used Table.Column to get the row with all EventIDs (e.g. Table with aggregated EventIDs). It worked as expected. But as soon as I update the data (new rows) or change some filters on visuals the sorting is going away and I although I get the right EventIds in my row, they are often falsely sorted. Is there any way to get the sorting (for Date/Time) inside my Table.Column Function. I tried but it seems not to work with simple Table.Sort.
I would be really helpful for your help.
Source TableTable with aggregated EventIDsTable.Column Formula
4 Replies
Hi,
try to sort your data directly in the M Query under "Edit Queries" -> Sort your Data until it is as expexted.
Notice that there are new Steps under "Applied steps" which will be always applied when the dataset is refreshed.
Thank you for the suggestion. Unfortunately it does not help a lot.
Sorting works generally fine with small data tables, but as soon as I have a little bit more complicated data, it does not seem to work. I work mainly with M Query and use the Microsoft Functions. I tried the List.Sort Function but similary it works fine with small data tables but if there is some more complex data it does not work anymore. It just sorts it false.
#"Added Custom1" = Table.AddColumn(#"Extracted Values", "Path_Date", each List.Sort(Table.Column([Table],"Date_Time"))),
