Hi,
I exported massive excel file into Power Query but the data does not maintain in row order like in the excel.
Excel Data:
Data Exported to Power Query:
As you will notice the data should end on entry: J-40325, but it actually ends on J-40727.
Is there any way to tell Power Query Editor to load the data in the same order as in the exported data from excel?
Assuming when you import from excel to Power Query the data is in the order you want, you can add an index column and sort by that.
@Nick_M thank Nick I followed your instructions and I added index column then sorted ascending
but still, data is not in ascending order once it’s loaded to the table. In order to fix the issue, I have to sorted the data once again manually in the table.
Is there any explanation why Query Editor is ignoring the command while loading the data to the table?
Hi @Matty
It could be Run Length Encoding that is resulting in a reordering of your table’s rows. With Run Length encoding, a column with a large number of repeated values is sorted so that equal values are grouped together. This means rows containing a common value can be internally referred to by start position & run length. This is part of compression algorithms used to reduce the memory footprint of a data model.
However to enforce some sort of order, you need a column containing values that can define the order, such as an Index column, a Date column, or some other column of sortable values.
With the steps and settings above in place it’s a puzzle to me why this doesn’t work for you…
Sorry I couldn’t be more helpful.
Thank you @Melissa for explanation. It seems not right even though the index column exist in data model, the order row number applies to your explanation. Once that data is loaded to the table then I have to filter once again by index number ascending, then it work but that step is repeated manualy rather than in Power Editor 
Yes I could but I am bit annoyed Power Query ingores my step while loading the data.
I am not sure if this is general issue with larger data.
Guess there’s one last thing you could try and that’s adding Table.Buffer
I know it’s not exactly what you’re trying to do but it explains it well
@Matty,
I’m trying to go off memory here, but what if you load the data from PQ to excel and then sort it there? I think that when you re-load the data it will stay sorted. Not sure that will hold, but worth a shot. Baring that, any chance you can upload some sample data of what you are working with? I feel like this should be possible but I use excel less and less these days.
@Nick_M Unfortunatelly I can’t share the data. I read the article that @Melissa has shared. The formula did not work for me. If I use the original data ( Columns A-Z contains a lot of text entries) the sort instructions that I apply in PQ does not apply when I load the data to the table even if in PQ I would delete columns that I don’t need. As I read in article " if you add a step in the code, and that step is not technically needed to produce the final result, then that step is actually never executed (even though it is there in the instructions) " - this is basically what is happening. Also as Melisa mentioned PQ groups value together to save memory.
If for example I would modify my data in excel file, deleting most of the columns then yes Table.Buffer works perfectly.
Thanks for your help guys, looks like I just have to add small manually step.
@Melissa
@Nick_M
Hey both I finally manage to find a solution.
Instead of loading the data model as a table,
Load query result to the Data Model + Only Creation Connection (not Table)
Create a Flattened Pivot Table from the Data Model, adding all fields, as if this would be a Table
Glad to hear you’ve solved it and thanks for sharing with the community!
Hi @Matty, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
