Dynamic Change Tracking for LMS Enrolments Using Power Query M

[techies]

While your Moodle LMS data provides a complete snapshot of daily users, course enrollments, and activity statuses, it obscures the vital story educators, administrators, and analysts really care about: what changes occurred since yesterday?

• Was a new student enrolled in the Data Analytics course?
• Did someone drop off the Power BI Advanced program?
• Which users became inactive or completed their courses overnight?

These are critical questions in learning management systems, student engagement tracking, and compliance reporting. However, a standard Moodle snapshot table does not directly show these transitions.

Traditionally, answering these questions involves complex SQL self-joins or intricate DAX measures. However, there is a much cleaner and more maintainable approach: Power Query.

In this post, we will build a snapshot change tracking solution using Power Query (M language) that automatically identifies added and deleted enrolments between consecutive snapshots.

Suppose we have a daily Moodle snapshot showing which students are enrolled in which courses:

Our goal is to track who joined or dropped courses over time, capturing Added and Deleted enrolments.

Step-by-Step Explanation

Sort Snapshots Chronologically

Sorted = Table.Sort(Source, {{"SnapshotDate", Order.Ascending}})

Ensures comparison flows sequentially day by day.

Extract Unique Snapshot Dates

Dates = List.Distinct(Sorted[SnapshotDate])

IndexList = List.Positions(Dates)

We prepare a loop to process each snapshot against the previous one.

Compare Consecutive Snapshots

= List.Transform(IndexList, each

        let

            idx = _,

            curDate = Dates{idx},

            prevDate = if idx = 0 then null else Dates{idx - 1},

            curTable = Table.SelectRows(Sorted, each [SnapshotDate] = curDate),

            prevTable = if prevDate = null then #table({"SnapshotDate","Student","Course"}, {}) else Table.SelectRows(Sorted, each [SnapshotDate] = prevDate),

This snippet iterates over each snapshot date using a List.Transform and retrieve the current index (idx). It assigns the current date from the Dates list as curDate and the previous date as prevDate (or null for the first snapshot).

Inside each iteration:

• curTable = current day enrolments
• prevTable = previous day enrolments

Detect Added Enrolments

Added = Table.SelectRows(curTable, each not List.Contains(prevTable[Student], [Student]))

Students present today but not yesterday → "Added".

Detect Deleted Enrolments

  DeletedRaw = if prevDate = null then #table({"SnapshotDate","Student","Course"}, {})

                         else Table.SelectRows(prevTable, each not List.Contains(curTable[Student], [Student])),

            Deleted = if Table.IsEmpty(DeletedRaw) then DeletedRaw else Table.AddColumn(DeletedRaw, "Status", each "Deleted"),

Students present yesterday but missing today → "Deleted".

Assign Effective Change Date

AddedAssigned = Table.AddColumn(Added, "ChangeDate", each curDate)

Marks the date when the enrolment change occurred.

Combine Results

We combine daily changes into a single table for easier analysis.

= Table.Combine(AllChanges)

The output provides a clear overview of who enrolled in or withdrew from each course over time.

The full M code is available here

Conclusion

Snapshot-based change detection is essential for analyzing evolving LMS data. Using Power Query, you can transform static Moodle snapshots into actionable insights, revealing trends and transitions in enrollments without the need for complex SQL or DAX.

By applying this dynamic, reusable pattern, Moodle administrators and analysts can track added and deleted enrolments efficiently, enabling better decision-making and proactive interventions.