BUG - Power Query: Table.Skip when Sorting

[Syrophenikan]

(File with bug is attached)

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.

The source table is only 360 rows.

 

[p45cal]

Two things to try:
1. Almost guaranteed to work; add an index column after sorting, remove top 10 rows, remove index column.
2. Add a Table.Buffer to the table sort line. Less likely to work.

Not on a PC at the moment so will revisit tomorrow, to look at your file.

 

[Syrophenikan]

I added an index column directly after performing the sort, and the sort order of the table changed. Why would adding a column of sequential numbers have any reason to change the order of the rows in the table? That makes no sense to me.

 

[p45cal]

I added an index column directly after performing the sort, and the sort order of the table changed.

Yes, I see!
Is which 10 rows get removed important? Or is it just that Power Query doesn't show what's expected?
If it's the former, then which 10 are you hoping to remove? The top ten Tubby Turtle rows in the order they appear in the original source table? Or some other? The sorting by only Inventory Item where there are more than 10 such items means that within a given item, the sorting is undetermined, and maybe all we have to do is determine that secondary (and tertiary etc) sort order.

 

[Syrophenikan]

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?

 

[p45cal]

should remove the First N rows regardless

Completely agree. Is this thread just to point this out? in which case you should report it to MS (as others have) and wait for an update from them.
If, on the other hand, you've discovered this while working on a project, you'll probably be looking for a robust workaround, sooner rather than later, which people here can probably help you with. If so, then addressing questions in post #4 would be an idea.
Which is it?

 

[Syrophenikan]

Thanks. I just wanted to make sure I wasn't misunderstanding the Table.Skip feature before I report a non-bug to Microsoft and look like an idiot.