Power Query: Group By function ignore previous sorting

[David_V]

Hello,

I've an issue with the group by function. It seems to ignore sorting from previous steps.

Example with a very simple data set (the goal is to keep the latest value for an ID):

raw data:

sorted data:

= Table.Sort(#"Changed Type",{{"date", Order.Ascending}})

After the group by:

= Table.Group(#"Sorted Rows", {"id"}, {{"nb", each List.Last([nb]), type number}, {"date", each List.Last([date]), type date}})

This should've keep the values for the 03/09/2017.

Thanks,

David

[MarcelBeug · Accepted solution]

One should always bear in mind that there may be a huge difference between the code steps and the way those code steps are actually evaluated when Power Query executes a query.

Your issue is similar to first sorting a table and next remove duplicates (Table.Distinct): it may be that not the first record will be kept.

Also the solution seems to be similar: buffer the table after sorting (and before grouping).
This will give you the expected results:

= Table.Buffer(Table.Sort(Source,{{"date", Order.Ascending}}))

[Anonymous]

Thanks for the information, I was looking for a logical issue in my code.
Nice solution, easy to understand and to the point.

[David_V]

Thank you for your response. I did find this workaround yesterday but:

1) is there a consequence in term of performances when dealing large amount of data (why is this not the default behaviour)

2) is there other cases when we should buffer the table (for instance when using Table.Distinct like you said in your post)

Thanks,

David

[MarcelBeug]

1) a - performance.

Table.Buffer is meant to improve performance.

Otherwise it will stop any query folding in which case performance may be degraded substantially.

1) b - why is this not the default behaviour.

That would be a question for Microsoft,

You can also ask: "Why would this be the default behaviour?":

grouping as such doesn't say anything about the sort order within the groups.

If you want to be certain, you can also select the maximum date during grouping:

let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"id"}, {{"AllRows", each Table.MaxN(_,"date",1), Value.Type(Source)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"date", "nb"}, {"date", "nb"})
in
    #"Expanded AllRows"

2) Other cases

Table buffering is highly recommended before initiating any loops with either List.Accumulate, List.Generate or recursive functions. In the latter case, also buffering is required within the recursive function.

Also before performing approximate lookups, the lookup table should be buffered.

Example code (untested) using approximate lookup: 

MyBufferedLookupTable = Table.Buffer(MyLookupTable),
AddColumn = Table.AddColum(MyTable,"LookupValue", (CurrentRecord) => Table.SelectRows(MyBufferedLookupTable, each [LookupValue] <= CurrentRecord[SearchValue]){0})

[David_V]

Thanks a lot. I understand a little bit better now and your examples are very interesting.