Table Sort , sort order , comparer

[Dicken]

Hi, can someone help with how  power query  sorts / compares  values;   so here i  have combined and sorted two tables;

let
  details = #table(
    type table [item = text, id = text, num = number],
    {{"c", "4", 3}, {"c", "3", 3}, {"c", "2", 4}, {"c", "1", 6}}
  ),
  all = #table(
    type table [item = text, id = text, num = number],
    {{"a", "", 12}, {"b", "", 53}, {"c", "", 55}, {"d", "", 23}, {"e", "", 33}}
  ),
  Custom1 = Table.Sort(Table.Combine({all, details }), {"item", Order.Ascending})
in
  Custom1

so when sorted by 'item', ascending; the 3rd value of details is first (id ; 2) the 'c" of "all"
then the rest of "c" details in descending order. so it's not just taking 'id' and then
stacking all of like values on top of each other,

a straight forward stacking can be achieved just by sorting on both, but this is different to how
xl would sort, i don't know if it is a sort of zip and compare , btw, it is same result regardless of combine order.
can anyone shed light on or links to how M engine sorts things, just interested ?

Richard. 

[v-abhinavmu]

Hi @Dicken,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @pcoley & @ralf_anton & @lbendlin for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[pcoley]

@Dicken 
Another possible solution to your issue is using Table.Buffer which forces Power Query to materialize the table in memory at that specific step. This has two relevant effects:

1. It fixes the current row order (prevents lazy evaluation surprises).
2. It can make the subsequent Table.Sort more predictable regarding tie-breaking when rows have the same value in the sort column.
   
   

let
  details = #table(
    type table [item = text, id = text, num = number],
    {{"c", "4", 3}, {"c", "3", 3}, {"c", "2", 4}, {"c", "1", 6}}
  ),
  all = #table(
    type table [item = text, id = text, num = number],
    {{"a", "", 12}, {"b", "", 53}, {"c", "", 55}, {"d", "", 23}, {"e", "", 33}}
  ),
  Combined = Table.Combine({all, details}),
  // Materialize the combined table → more predictable behavior
  Buffered = Table.Buffer(Combined),
  Sorted = Table.Sort(Buffered, {"item", Order.Ascending})
in
  Sorted​

[pcoley]

Power Query's Table.Sort is stable and uses a multi-key comparison, but when you sort only by one column, the relative order of rows with the same value in that column is not guaranteed to preserve the original order from Table.Combine.

In your example, the "c" rows from the two tables are getting interleaved in a way that feels inconsistent.

Why this happens

1. Table.Combine basically appends the rows (it tries to keep the order of the input tables, but it's not a strict guarantee in all cases).
2. Table.Sort is a stable sort in theory, but in practice when you only specify one column, the M engine compares rows using their internal representation.
3. When two rows have the exact same value in the sort column (item = "c"), the comparison often falls back to other columns or internal row order, which can produce results that look "random" or different from Excel.

   This is different from Excel, which is usually more predictable with "sort by this column only" while preserving original order for ties.

   How to get predictable / Excel-like behavior

   If you want all rows with the same item grouped together (and within the same item, a predictable order), you should sort by multiple columns:

let
  details = #table(
    type table [item = text, id = text, num = number], 
    {{"c", "4", 3}, {"c", "3", 3}, {"c", "2", 4}, {"c", "1", 6}}
  ), 
  all = #table(
    type table [item = text, id = text, num = number], 
    {{"a", "", "12"}, {"b", "", "53"}, {"c", "", "55"}, {"d", "", "23"}, {"e", "", "33"}}
  ), 
  Combined = Table.Combine({all, details}), 
  Sorted = Table.Sort(
    Combined, 
    {{"item", Order.Ascending}, {"id", Order.Ascending},  // secondary sort
    {"num", Order.Ascending} // tertiary sort (optional)
    }
  )
in
  Sorted

[ralf_anton]

Hi,

möglicherweise habe ich Dich falsch verstanden, aber die Liste der Spalten und deren Sortierreihenfolge beschränkt sich nicht auf nur eine Spalte. 

Meintest Du vielleicht etwas in der Art?

= Table.Sort(Table.Combine({all, details }), {{"item",Order.Ascending},{"num", Order.Descending}})

[lbendlin]

You gave incomplete sorting instructions - only sorting by item.  That gives Power Query the liberty to sort the rows within each group lazily. There will be no guaranteed sort order within each item group.

For more details refer to Ben Gribaudo's primer (best resource on Power Query)

Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo