Wonde Integration with Power BI using the API

Introduction

For Power BI users in a school or MAT environment, especially users of SIMS, Wonde can offer several advantages over CSV files or even the more powerful native capabilities of Bromcom and Arbor. The advantages include:

• No need to use a gateway to access locally hosted SIMS CSV files
• A standardised way of accessing data that’s the same for all schools across your trust
• Low cost
• Easy to set auto refresh in the Power BI service

The main disadvantage is complexity. Using the Wonde API with Power BI is advanced stuff. Having said that, if I can do it, so can you! These instructions will point the way, but they do require you to be an existing, experienced user of Power BI.

Register for a Wonde Developer account

Full instructions are on Wonde’s website. Doing this gives you access to Wonde’s API documentation, including a sandbox account for you to test your code.

Set your Wonde developer account up

Wonde is aimed at external developers who need to jump through lots of hoops to access your data. Once you have completed the sign up process, you need to set out the data access permissions you requires. My advice to to just request the permission you know you will need for the immediate report. Also, no need to request any write permissions – Power BI can’t write back any data. The outcome of this process will be your school data will be set up for you to access. To access the data you will need to generate a Wonde token to use with your Wonde school ID.

Create two parameters in Power Query Editor

These two parameter match the read token and your school’s Wonde ID. See below:

Create functions to access a ‘page’ of data

Now we can use PQE to create some functions that will download a page of pupil data. As a default each page will contain 20 rows of pupil data. To create a function, create a ‘blank query’ and just paste this code in it. Call this function fGetStudents

(PageNum as number)=>
let
    apiUrl = "https://api.wonde.com/", 
    relpath = "v1.0/schools/" & WondeSchoolId & "/students" ,
    header = [#"Authorization"="Bearer " & WondeToken ],
    result = Json.Document(
        Web.Contents(
            apiUrl, 
            [
                RelativePath=relpath, 
                Headers = header, 
                Query = [page=Number.ToText(PageNum), include="extended_details,registration,attendance_summary,education_details,year,contact_details"]
            ]
            )
        ),
    data = result[data]
in
    data

Notice the query section tells Wonde to also return student data from other tables – Wonde does not store all the pupil data in one convenient table, you have to add the required tables to the query list. See Wonde’s API documentation for more details. You will need functions like this for attendance marks, pupil leavers, attendance leavers, pupil contact details and perhaps other purposes.

Incorporate the function into a query

Now you have your function, you need to call it from the query. You can’t just call it once, because it only return 20 rows of data, so you need to call it in a loop. In PQE the list.generate function does this, looping round and calling the function until it runs out of rows. Again, create blank data query and paste this code in:

let
    Source = List.Generate(()=>
        [Result = try fGetStudents(1) otherwise {}, Page=1],
        each [Result]<>{},
        each [Result = try fGetStudents([Page]+1) otherwise {}, Page=[Page]+1],
        each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1", "Data"}})
 in
    #"Renamed Columns"

Repeat for each other data type as follows

Attendance Marks

Attendance marks need a bit more work – Wonde stores the marks as it’s own codes, not the / and \ marks that we are familiary with. So you first need to access the Wonde attendance codes tables. Create this function, which I’ve called fGetAttendanceCodes

(PageNum as number)=>
let
    apiUrl = "https://api.wonde.com/", 
    relpath = "v1.0/schools/" & WondeSchoolId & "/attendance-codes" ,
    header = [#"Authorization"="Bearer " & WondeToken ],
    result = Json.Document(
        Web.Contents(
            apiUrl, 
            [
                RelativePath=relpath, 
                Headers = header, 
                Query = [page=Number.ToText(PageNum) ]
            ]
            )
        ),
    data = result[data]
in
    data

Create a new query to call this function, I’ve called mine Wonde Attendance Codes (note that it isn’t very big, so no need to loop through the pages, just page 1 should be sufficient

let
    Source = fGetAttendanceCodes(1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "code", "description", "type"}, {"Column1.id", "Column1.code", "Column1.description", "Column1.type"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.id", "Wonde Attendance Code"}, {"Column1.code", "Attendance mark"}, {"Column1.description", "Attendance Mark Description"}, {"Column1.type", "Attendance Mark Statistical Meaning"}})
in
    #"Renamed Columns"

This table is only small, your should look something like this:

Now we have Wonde’s attendance codes, we can create a function to get the actual marks. Call this function fGetAttendanceMarks

(PageNum as number)=>
let
    apiUrl = "https://api.wonde.com/", 
    relpath = "v1.0/schools/" & WondeSchoolId & "/attendance/session" ,
    header = [#"Authorization"="Bearer " & WondeToken ],
    result = Json.Document(
        Web.Contents(
            apiUrl, 
            [
                RelativePath=relpath, 
                Headers = header, 
                 Query = [page=Number.ToText(PageNum), attendance_date_after=fDate18MonthsAgo ]
            ]
            )
        ),
    data = result[data]
in
    data

Next create a query to read in the attendance marks. Call this table Wonde Attendance Marks:

let
    Source = List.Generate(()=>
        [Result = try fGetAttendanceMarks(1) otherwise {}, Page=1],
        each [Result]<>{},
        each [Result = try fGetAttendanceMarks([Page]+1) otherwise {}, Page=[Page]+1],
        each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "date", "comment", "session", "employee", "attendance_code", "student"}, {"Column1.id", "Column1.date", "Column1.comment", "Column1.session", "Column1.employee", "Column1.attendance_code", "Column1.student"}),
    #"Expanded Column1.date" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.date", {"date", "timezone_type", "timezone"}, {"Column1.date.date", "Column1.date.timezone_type", "Column1.date.timezone"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.date",{"Column1.id", "Column1.date.timezone_type", "Column1.date.timezone", "Column1.comment", "Column1.employee"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.student", "Wonde Student ID"}, {"Column1.attendance_code", "Wonde Attendance Code"}, {"Column1.session", "Session"}, {"Column1.date.date", "Attendance Mark Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Attendance Mark Date", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Wonde Attendance Code"}, #"Wonde Attendance Codes", {"Wonde Attendance Code"}, "Wonde Attendance Codes", JoinKind.LeftOuter),
    #"Expanded Wonde Attendance Codes" = Table.ExpandTableColumn(#"Merged Queries", "Wonde Attendance Codes", {"Wonde Attendance Code", "Attendance mark", "Attendance Mark Description", "Attendance Mark Statistical Meaning"}, {"Wonde Attendance Code.1", "Attendance mark", "Attendance Mark Description", "Attendance Mark Statistical Meaning"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Wonde Attendance Codes",{"Attendance Mark Date", "Session", "Wonde Student ID", "Attendance mark", "Attendance Mark Description", "Attendance Mark Statistical Meaning"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Other Columns",{{"Wonde Student ID", "External Id"}, {"Attendance mark", "Mark"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Attendance Mark Date", type date}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Attendance Mark Date", "Mark date"}, {"Session", "AM/PM"}})
in
    #"Renamed Columns2"

Note the ‘Merged Queries’ step where we match Wonde’s attendance codes to the codes we extracted in the previous step.