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

Bernard McGarvey 1 Reputation point
2021-03-10T13:59:37.377+00:00

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,538 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Lz._ 8,991 Reputation points
    2021-03-11T17:12:13.52+00:00

    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...

    0 comments No comments

  2. Bernard McGarvey 1 Reputation point
    2021-03-14T22:30:22.753+00:00

    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


  3. Bernard McGarvey 1 Reputation point
    2021-03-16T17:19:08.607+00:00

    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

    0 comments No comments

  4. Lz._ 8,991 Reputation points
    2021-03-16T18:07:18.977+00:00

    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

    0 comments No comments