Table refresh removes Data Validation value from previous last row and pastes it to newly imported last row

Question

I have an excel table in a workbook that is derived via Power Query. A simple table, 13 columns, 400 rows currently.
It shouldn't matter, but for background: This is trading data. It is taken from my trading platform, saved in a text file, brought into the data model via power query, sorted, filtered, and condensed, then loaded to Excel.

I have added two columns to the table which are Data Validation drop downs from lists.

The data validations work fine. When I refresh the data, new data comes in and populates new rows. The appended data validations are available on the new rows.

However, If I assigned a value to one of these data validation fields the last row of data, then refresh the data adding new rows, the value contained in the previous "last row" of data is cut from that row and pasted into the new "last row's" data validation field.
It appears to be dragging the values down, but also removing them from that previous last row.

Feels like a bug, but I'm wondering if I need a vba work around.

As always, TIA for sharing any insight.

Answer 1

Could you share a test file to reproduce your issue? Then I can give suggestions on it.

For sharing file, you may upload it to OneDrive or any other cloud drive and then post the link here.

*Please make sure you have removed any sensitive or private information in the sample file before uploading.