Values in table change when expanding table column

[Anonymous]

Hello,

Didn't find anything via the search function...
I'm running a query with the following source code:

let
    Quelle = Latest_Tickets,
    #"Gefilterte Zeilen" = Table.SelectRows(Quelle, each ([Status] = "In Process")),
    #"Gruppierte Zeilen" = Table.Group(#"Gefilterte Zeilen", {"Ticket Type", "Processor"}, {{"Latest_Ticket_By_Type_By_Processor", each Table.RowCount(_), type number}}),
    #"Zusammenführte Abfragen" = Table.NestedJoin(#"Gruppierte Zeilen", {"Ticket Type"}, Order_Ticket_Type, {"Type"}, "Order_Ticket_Type", JoinKind.LeftOuter),
    #"Erweiterte Order_Ticket_Type" = Table.ExpandTableColumn(#"Zusammenführte Abfragen", "Order_Ticket_Type", {"Reihenfolge"}, {"Order_Ticket_Type.Reihenfolge"}),
    #"Sortierte Zeilen" = Table.Sort(#"Erweiterte Order_Ticket_Type",{{"Order_Ticket_Type.Reihenfolge", Order.Ascending}})
in
    #"Sortierte Zeilen"

Within the marked code, the values change in the table. I don't konw how to describe it better:

Value at "Zusammengeführte Abfragen"Value at "Erweiterte Order..."

Would anybody have an idea why this occurs?!

[ImkeF · Accepted solution]

Hi @Anonymous ,

there is no native sort order in Power Query and the joins do often reorder the rows.

If you want to keep a sort order from the original load, you have to add an Index-column right at the beginning after which you can sort later on, once PQ has changed the row.

But make sure to buffer the sort, as the sort order might not be kept otherwise: https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390 

[Anonymous]

@ImkeF  Could you give additional insight on this as well as when is the best time to add the Table.Buffer step? I'm having the same thing happen, and I sort the data by Ascending Created Date right when I load it in from the data warehouse. Do I have to add a Sort/Buffer step after every merge? Just at the end? It's almost like its shifting and/or re-evaluating.

[TaniaBecerra5]

thank you this also helped me!

[Nathaniel_C]

Hi @Anonymous ,

Let me suggest that you reach out to @ImkeF  who is a genius with queries!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel