Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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...
Need Help
WolfBiber's avatar
WolfBiber
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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"))),
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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 ????

     

  • WolfBiber's avatar
    WolfBiber
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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.