Forum Discussion
Augment data following append query
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
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
1 Reply
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
