Augment data following append query

[Karim Masarweh]

Hi there,
We have several team members, each maintaining his/her own data in an excel file.
Managers are using an append Power Query (on excel 2013), to consolidate the data and create reports.
My question is: what if someone who uses this append result wants to augment the data, for example for assigning each row to a team. He wants to work on the result from the append query and not access the single files.
Adding columns to the result sheet where the append result is loaded, doesn't seem like a solution. The problem is that , since the data is growing in each file, the sequence of the rows in the query result is not maintained.
For example, file 1 has row with ID=A and file 2 has row with ID=B. The append result will be A,B.
If row C is added to file 1 and row D is added to file 2, then the append result will be A,C,B,D.
hence the assignment made on the second row initially for row B will now be for row C!
Any suggestions? What is the best way to augment data in this case?

Thanks a million,
Karim Masarweh

[#2]

Hi Karim

Ideally I'd add a "Assigned Team" field to my team members' source files to make it nice and simple.

However if you can't do that then It sounds like you'd need to add a reference column in the appended query such as FileRow

This would then give

File1Row1

File1Row2

File1Row3

File2Row1

File2Row2

File2Row3

You could then maintain a separate table that would be set up like this

Reference    Assigned to

File1Row1   Person A

File1Row2   Person A

File1Row3   Person B

File2Row1   Person B

File2Row2   Person C

File2Row3   Person D

Then merge this new table with the append table