I am fairly new to Power query and power pivot.
I export data every night from our MIS to create automated dashboards for our leadership team but this new report has me stumped.
I am trying to create a report based on all students who have been on roll in our school since the start of term. The problem is that once a student leaves, some data becomes hidden and cannot be accessed via the reporting options within the MIS.

My idea was to create a "basedata" table by running the export and saving this one table at the start of the year. Every night I would then run a report with all students on roll that day, any new students would be added to the base data table via an append query. Any students already on roll would be ignored and any leavers would not appear in the on roll list so would not change the base data table. That gives me a table which contains all students who have been on roll since the start of the year - problem nearly solved.

The issue is that some of the data that becomes hidden once a leaving date is entered is updated throughout the year. not often, but it happens. This updated data will be in the daily report so how do I update the basedata table for any students with updated data in the overnight run?

Thank you in advance for your help

Sue