Adding a blank column & retaining data

Hi Ful56 

Nope sorry you won't be able to do that.  If you add a blank column in PQ and load that to your Excel sheet, then type data into that column - every time you refresh the query, that column will be overwritten with the blanks from the query/table inside PQ.

Regards

Phil

Thanks PhilipTreacy is there away of keeping a column sync on the sheet out side of PQ so if a new student is added it moves the notes column notes down so they stay with the student?

Hi Ful56 it's possible if you have or create unique key. You'll need to load PQ data to the sheet, add column for comments, load table back to PQ and merge original query with the last one using unique key. Well known self referencing queries/tables. Check this out.

Yes, it is possible to exclude the column from overwriting the data stored in the sheet. You can do this by creating a new query that combines the attendance data with the notes data and then outputting the combined data to a new sheet in the same Excel workbook.

Here's an example of how you can do this:

1. Open the Excel workbook with the attendance data and open Power Query.

2. Load the attendance data into Power Query.

3. Create a new query that reads the notes data from another source, such as a separate Excel workbook or a database.

4. Merge the attendance data query and the notes data query using a common column such as the student ID.

5. In the merge options, select the option to keep only the columns from the attendance data query.

6. Add a new column to the merged data that contains the notes for each student.

7. Load the merged data into a new sheet in the same Excel workbook, but exclude the column with the attendance data that you want to keep.

8. Save the Power Query changes and refresh the data when needed.

By following these steps, you can add a notes column to your attendance data without overwriting any of the data in the original sheet.