Sorting Excel Table doesnt sort the non-table data; Query autofills manually edited cells with formu

[Rohan110]

Hi,

I'm using Query to automatically update my data from A to D (Consider D header to be Category)columns. I have a column on E whose formula is basically =Table_Categorizer[@Category]

Problem i have is some cells in E is manually entered. But every time i refresh, the manually entered data gets automatically changed to the formula =Table_Categorizer[@Category].

Even if i make the column E not be part of the table, the problem i'm facing now is when i sort any column data from A to D, the Column E doesn't get sorted. This causes syncing issues with data. 
Data in D Column is actual data, and in E column is Actual + manually altered data. I want these two rows to be always in sync and never be automati

[#2]

Hi @Rohan110 

Assuming I understood you're in front of a well known problem/limitation that has no reliable solution if your Source table doesn't have Unique IDs - see if Matt's approach can help you...

[#3]

@L z. 

Hi tried the above method but it still doesnt work.

The first cell in the Manually changed category has the formula =[@Category]. So every time i refresh, the eating out value changes to the above formula.

[#4]

Hi @Rohan110 

As I understand you want to make exception(s) to the "rule" (=[@Category]). AFAIK this isn't reliably possible. Unchecking "Preserve column sort/filter/layout" (in Data > Properties) has some effects...