BUG - Power Query: Table.Skip when Sorting

[bryonsmedley]

Potential bug in Power Query's Table.Skip function. Tested in the following:

• Excel - using version 2.120.7004.1 64-bit
• Power BI - using version 2.119.870.0 64-bit (July 2023)

When removing rows from the top of a table (for example, remove the top 10 rows) using the Table.Skip function, the first 10 rows are deleted as expected.

However, if the step before the "remove 10 rows from the top" is a sort function using Table.Sort, the results are wildly unpredictable. Various unrelated rows from the table are deleted and the result is resorted by what appears to be total randomness.

If you sort by multiple criteria (ex: sometimes sorting by two columns, other times by three or more), the Table.Skip function works as expected, removing the top N rows from the table.

Removing the Table.Sort step makes the Table.Skip function work properly.

To my expectation: the Table.Skip using a number as the argument should remove the First N rows regardless of the content. The content should have no bearing on which rows get removed. It should be the First N no matter what. What am I missing here?

The source table is only 360 rows.

[briceem]

According to officiel documentation : "Buffers a table in memory, isolating it from external changes during evaluation. Buffering is shallow. It forces the evaluation of any scalar cell values, but leaves non-scalar values (records, lists, tables, and so on) as-is."

I faced this issue before applying a Table.Distinct. 
My assumptions is because of some internal under the hood optimization, PQ is applying the triage (Table.Distinct, Table.Skip, ...) before applying the sorting operation.

About the fact that this is more a workaround, I follow you on this. In my case (few lines, like you) it is ok and work as expected so I didn't dig deeper !

[briceem]

I just found this while searching another tyhing !

https://learn.microsoft.com/en-us/power-query/common-issues#preserving-sort

Preserving sort

You might assume that if you sort your data, any downstream operations will preserve the sort order.

For example, if you sort a sales table so that each store's largest sale is shown first, you might expect that doing a "Remove duplicates" operation will return only the top sale for each store. And this operation might, in fact, appear to work. However, this behavior isn't guaranteed.

Because of the way Power Query optimizes certain operations, including skipping them or offloading them to data sources (which can have their own unique ordering behavior), sort order isn't guaranteed to be preserved through aggregations (such as Table.Group), merges (such as Table.NestedJoin), or duplicate removal (such as Table.Distinct).

There are a number of ways to work around this. Here are a few suggestions:

• Perform a sort after applying the downstream operation. For example, when grouping rows, sort the nested table in each group before applying further steps. Here's some sample M code that demonstrates this approach: Table.Group(Sales_SalesPerson, {"TerritoryID"}, {{"SortedRows", each Table.Sort(_, {"SalesYTD", Order.Descending})}})
• Buffer the data (using Table.Buffer) before applying the downstream operation. In some cases, this operation will cause the downstream operation to preserve the buffered sort order.
• Use ranking. For example, instead of using Table.Distinct, you could order by the column(s) containing the duplicate values, rank based on a tie-breaker column (such as modified_date), and then filter to keep just the rank 1 rows.

[briceem]

hi,

I faced the same issue. Just use Table.Buffer() arround your Table.Sort; for example:

#"sortedArray" = Table.Buffer(Table.Sort(#"previousStep",{{"column", Order.Descending}})),

[bryonsmedley]

That worked...

...but can you explain what the Table.Buffer is doing to make the sort perform differently?  The average PQ user is NOT going to think to do this.  I would still consider it a bug with a workaround.  Thanks, in advance.