My data sorts correctly in preview but totally different when loaded to Excel

[Keezz]

Just checking if this is a known bug, If it isn't I'll investigate further or supply more details.

Here is what happens:

• My Table has 66 rows
• I add an index form -1 by -1
• I do a merge with full outer join,
• I do and expand which messes up my original sort order
• Table is now 143 rows
• I sort my table on the index column Descending to get it back in the original order
  Some Index values are null from the outer join, so these are sorted last, which is what I want
• I remove the index column (and 2 other columns)
• The preview is perfect
• I load the resullt into an Excel table
• The order in Exel is completely different!

What is happening here???

[Deku · Accepted solution]

Try adding a table.buffer() at the end of the query to cache the results

[Keezz]

Thanks @Deku , that did the trick!

Is this considered to be a work-around for a bug? And if so, has this bug already been reported?

[Deku]

Not a bug, just how PowerQuery is implemented 

[v-aatheeque]

Hi @Keezz ,

we wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

If our response has addressed your query, please accept it as a solution so that other community members can find it easily.

[Keezz]

@v-aatheeque , you are a bit impatient, aren't you? I happen to have other duties beside Power Query work, so I hadn't had the opportunity to review...

[ZhangKun]

If it is not the first time to load into the table, the data will be sorted according to the settings on the table after loading.

If it is the first time to load, or the keep sort option of the table is turned off, it should be in the order displayed in the Power Query interface.

If none of the above conditions are met, this may be a bug. However, it is not ruled out that some operations may be optimized by Power Query, resulting in different displays in Power Query and Excel.

[Keezz]

Hi @ZhangKun thanks, but I had already tried that. I removed the table and even removed the columns of the worksheet where the table used to be. After editing the load-to and re-creating the table, same problem!