Load To.. saving the query contens in a different order

Question

I have a Power Query that when I set it up originally saves the data to a data table in the same order as it shows when I look at the query output prior to the "LoadTo" step. I then changed the input (.csv) files for the query so that there is new data. When I do a refresh, the data loads into the Table on the tab as before but now the order of the rows on the Table in the tab is not the same as the order shown on the query just before the "LoadTo" step.

I don't know why the order has changed. I refreshed the query multiple times and it gives the same ordering of the rows in the table which again is different than what shows in the Query window. I can reorder the rows in the table with a sort command but I would like to know why this change of order occurs in case I am doing something incorrectly?

Any idea what might be happening to cause this?

Answer 1

Hi @Bernard McGarvey

(just in case) The PQ Editor only provides a preview of the 1st (1K top of my head) rows. Not sure what else to say given the information you provided (no query code)

Merging and Grouping operations cannot guarantee an order set of records. If you do any of them this could explain what experience

Other thing, do you Add to the Data Model in addition to loading the query output to a worksheet? It took me a moment to find it out but this case I dealt with some time ago reminded me something...

Answer 2

So if I read your answer correctly this behavior is not unexpected. That's really all I needed to have confirmed. I can live with sorting the final table myself now that I know it is not due to an error on my part.

Thanks

Answer 3

Here is the query code:

let
Source = Table.NestedJoin(#"Cold Attr Profile Stats", {"Row_Num"}, #"Cold Profile QT Stats", {"Row_Num"}, "Cold Profile QT Stats", JoinKind.LeftOuter),
#"Expanded Cold Profile QT Stats" = Table.ExpandTableColumn(Source, "Cold Profile QT Stats", {"Row_Num", "QT Hours°C", "QT(Min) Hours°C", "QT(Max) Hours°C", "TSS °C"}, {"Cold Profile QT Stats.Row_Num", "Cold Profile QT Stats.QT Hours°C", "Cold Profile QT Stats.QT(Min) Hours°C", "Cold Profile QT Stats.QT(Max) Hours°C", "Cold Profile QT Stats.TSS °C"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Cold Profile QT Stats",{"Cold Profile QT Stats.Row_Num"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Cold Profile QT Stats.QT Hours°C", "QT Hours°C"}, {"Cold Profile QT Stats.QT(Min) Hours°C", "QT(Min) Hours°C"}, {"Cold Profile QT Stats.QT(Max) Hours°C", "QT(Max) Hours°C"}, {"Cold Profile QT Stats.TSS °C", "TSS °C"}})
in
#"Renamed Columns"

Thanks

Answer 4

Hi @Bernard McGarvey

From my 1st reply: Merging and Grouping operations cannot guarantee an order set of records. If you do any of them this could explain what experience
And the 1st step in your query does a Merging operation (Table.NestedJoin)...

If I don't make mistake in your column names, your query can be simplified as follow:

let  
    Source = Table.NestedJoin(  
        #"Cold Attr Profile Stats", {"Row_Num"},  
        #"Cold Profile QT Stats", {"Row_Num"},  
        "Cold Profile QT Stats", JoinKind.LeftOuter  
    ),  
    #"Expanded Cold Profile QT Stats" = Table.ExpandTableColumn(Source,  
        "Cold Profile QT Stats",  
        {"QT Hours°C", "QT(Min) Hours°C", "QT(Max) Hours°C", "TSS °C"}  
    )  
in  
    #"Expanded Cold Profile QT Stats"  

Hope this makes sense & helps