Forum Discussion
Excel and Power Query, order altered when new data gets added on refresh
Hi Anonymous
Sorry, I don't quite understand the problem you are experiencing. Do you hope to always have the newly added rows at the bottom? If so, this cannot be guaranteed. In Power Query, every time when you refresh the data, it will load all data from data sources to replace all existing loaded data in the model. So it doesn't know which rows are newly added in the data source. When storing data, it follows columnar storage instead of row storage, so it doesn't care about the row ordering, which means the order of rows may change.
If you want the rows to display in a specific order, you have to add an explicit step to sort rows by one or more columns. From your screenshot, it seems the rows are sorted by the fourth datetime-type column currently. You probably need to sort rows by some other columns to have the desired outcome you want. But note that it cannot guarantee the latest added rows are at the bottom unless you have a column recording Edit time or Recorded time and sort rows by this time column.
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
The data is notifications that comes in every day, so the order is current date > older date. Before i had them ordered on date from newest to oldest just in case, but that made the new columns order invert on each refresh, like those attached files on column 2 would jump to the first rows, then to the bottom on each refresh.
As you say the problem seems to be that new data pushes old data down, but excel doesnt know what to do with the current columns, so it just creates blanks at the bottom, not exactly the bottom since it leaves the last row as it was.
I tried adding the new columns on the query but data gets overwriten on refresh, now i tried to inverse the order on query, so new data gets added on the bottom rather than push down the previous rows, not ideal tho since i needed to see the data from newest to oldest. Ill check once a new notification arrives if it keeps the order.
Any other idea how could i tackle this? I need to add info in other columns for each row but they need to stay with that row when new data gets added.