Power query: how to add (manual) data that doesn't disappear when refreshing.

[MeikeR]

I am trying to use powerquery to make a holiday planner for our team, but I encountered some problems. The idea is that we can add new/remove colleagues in a team members table, refresh the query and the name is added in the monthly tables. The team member will fill his absence manually. With this last part I encountered the problems. I tried it in two ways and failed both times. A solution to one of the problems will be most helpful! (I added the file to this message). 

1. I merged the team members and month format in a query. This resulted in tab Test1. When I fill the table with data, this data will disappear when refreshing the table: how can I refresh the query without losing the manual data? 

2. I used only the team members table as a query. This resulted in Test2. This time the data stayed put when I refresh the query, but when I add or remove a team member, there seems to be no connection between the team member columns and the other columns. So for example, when you add Name2 between Name1 and Name3, Name2 will be in the row of Name3 which contains the data of Name3. Name3 will be in row 4, with all the data of 4. So how can I (automatically) add a whole row instead of just moving up the first columns? 

In addition to this: does anyone know why the 'subteam' kolumn turns black after I refresh it? If I refresh it again, it gets back to normal. 

[#2]

@MeikeR I'm not an expert, but here's my take on at least part of your question.  When you merged the two tables to generate the Team/Date query, you just made a largely blank query.  You then entered data inside the blank query, which is possible, but doesn't change the underlying query.  When you update the Team table, the query automatically regenerates as blank, because that's all the data it has in the original tables.  If you used the Team/Date query area as a new query source, the new query would include any data you had typed into the Team/Date cells.

[#3]

@MeikeR I did a little more on this, although I'm a bit hampered by not really understanding where you want to go with this.  Anyway, I basically just converted everything into queries to get what I thought you were after.  

The Members and Days tabs are used to generate the Data Entry tab using "append".  Put the data for the individual days here.  Gen2 is just a query containing the Data Entry information and is only included to better show how this concept is working.  Present is just the Members table manually copied with some rows deleted from the middle.  Final is the merged query of Gen2 and Present, using a merge including all of Present and matching from Gen2.