Student Data Sync, or SDS, is a core tool from Microsoft that helps schools prepare their student, teacher and class data ready for use in great platforms such as Teams for Education (formerly Microsoft Classroom) and Intune for Education.

In countries outside of the USA (where API exist), schools need to prepare six CSV files containing the relevant information from their Student Management System (SMS). Fortunately, Microsoft has provided some sample scripts and files (along with a toolkit to verify your data integrity) to help.

Student Data Sync is the starting point to creating a correlation or framework that connects your students, teachers and classes together in a meaningful way, allowing you to leverage cloud based tools more efficiently.

However, often the challenge lies in the format of the exported data from the school’s SMS. This is where Grant Saul, the Director of ICT from Westlake Boys High School has stepped in and provided a fantastic tutorial on how to use Power Query, a tool that comes in Excel 2016, to tidy up the format of your source data and prepare it for import with Student Data Sync.

In Grant’s example, he takes a standard export from Kamar (a very popular New Zealand SMS) and shows how it can be transformed using Power Query into the correct format for importing into SDS. You can read his original post here (and I encourage you to do so) whilst watching his screencast below:

The great feature of Power Query is it records each step in the data transformation, allowing you to easily replicate / replay the changes when the source data is refreshed, creating a super efficient method of managing your data.

For schools that want to use Microsoft SDS this is a very helpful guide.