Forum Discussion
Combining power query with manual data entry
I work in education and my team has a master spreadsheet with thousands of students on it. I manage a subsection of these students and want to be able to record a lot of information which is only needed for this particular subsection - this would clog up the master spreadsheet for a relatively small cohort of students so it's not going to be appropriate to add all the fields I need to the master.
I created a power query to pull my subsection of students into another spreadsheet, but I now want to be able to manually add additional information to the new spreadsheet. For example, I'll want a column to track when I've enrolled the student in our virtual learning environment, and this will just be a case of me inputting 'Y' for each student as and when they've been enrolled.
I tried simply adding additional columns to the table and that works fine until I refresh the data, and then the columns I've entered manually don't get sorted along with the data that's been pulled from the master.
Is there another way to do this?
Hi lloyda5,
You could create a new table by entering data in Power Query.
Or create the data in another tool, such as Excel, and import it into Power Query.
In your case, Merge Queries may be more suitable.
Below is an example.
Sample data:
Now suppose you need to add a column about student grades. This is where you can merge the table about student grades with your main table, and the matching column is your student name.
Student grades:
Merge:
Expand it.
Merge queries overview - Power Query | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Syndicate_Admin My similar situation and solution:
for manual entry I created separate table and import it in PQ then I combine it using append join as my data should be column format.
I assume you will need some kind of join / merge. Hope this help
Thanks for your reply. But where do you do the manual data entry? In the separate table you create, or in the combined table you have merged it with using PQ?
Syndicate_Admin in separate table insert amount, then import it via PQ and last step merge it with rest of other tables.
Hi lloyda5,
You could create a new table by entering data in Power Query.
Or create the data in another tool, such as Excel, and import it into Power Query.
In your case, Merge Queries may be more suitable.
Below is an example.
Sample data:
Now suppose you need to add a column about student grades. This is where you can merge the table about student grades with your main table, and the matching column is your student name.
Student grades:
Merge:
Expand it.
Merge queries overview - Power Query | Microsoft Learn
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
