Adding a blank column & retaining data

[Ful56]

Hi 

I have a attendance excel document which I'm getting data from a third party source but I want to add a notes column so they can add notes against a student. 

problem is I cant do this in the third party so I though I'd add a blank column in power query but after a refresh. It blanks that column in the sheet. Is it possible to exclude the column from over writing that data stored on the sheet

[Adamboer]

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.

[Ful56]

Thank you for the guidance, we dont have the notes yet as the idea is the staff will use the attendance data and make comments againest a week or a term column that contains attendance data. as the notes column isnt coming from PQ and is within the excel sheet next the attandance data column, when a new student joins the attendance columns all shift down which is great as its in sync as its coming from pq but the attendance notes do not.  

[AlienSx]

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.

[Ful56]

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?

[PhilipTreacy]

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