Understood. Thank you so much for clarifying here!
In my example, query1 is the data in the table and is pulling employee data and query2 is a separate query pulling in user names. Anywhere you see an employee number, e.g. "Employee 1", it's coming from the query1 and anywhere you see a real name, e.g. "Timon", it's coming from query2.
I tested your setup with a deprecated dropdown component and I believe the feature you're looking for is something like a Mapped value (red), is that correct? That would satisfy your first bullet point. And values + labels (blue) satisfy your second bullet point.
In the new dropdown type column (Single Tag), there is no explicit Mapped Value field, but since the table data field is using query1.data (where the "Employee 9" type values are coming from), it persists that as the column values (red).
Then, in the column settings, you can change the data source to reference query2 (where the first names are coming from) and set the values and labels (blue).
I've passed this feedback along to the team that worked on this feature, and please do let me know if there's anything else I should pass along or anything I can help clarify! 