Aggregating data from multiple columns in one row (dynamic sorting)

[Anonymous]

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

[WolfBiber]

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.

[Anonymous]

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"))),

[WolfBiber]

Good Moring,

can you provide us some test data?

I'm guessing why you're sorting "Path Date" and not something like

= Table.Sort(#"Extracted Values",{{"ID", Order.Ascending}, {"EventID", Order.Ascending}})

Also, in youre example above: are you sure all data types are set correctly? It is essential that Data Types are set correctly for a correct sorting.

You can also do the grouping and concat in M->

= Table.Group(#"Extracted Values", {"ID"}, {{"Concat", each Text.Combine(List.Transform([EventID], (x)=>Number.ToText(x)),";"), type text}})

 I hope it helps you.

[Anonymous]

Does anybody know if there is a way to do it as calculated column in DAX. I mean grouping the data for each Event in one row and sorting it with help of other column (date) beforehand ??? Something like Table.Group and Table.Sort for DAX ????