How to use Power Query to Transform MIS Student Assessment Data for Simpler Power BI Reports

This is a MS Excel export from the iSAMS school management information system. It’s actually a pivot table that contains student assessment data that we want to use in a Power BI report.

The problem with the export is the format. Power BI works best when the source data is a simple flat table - just column headers and no row headers. That is not what we’ve got:

We’ve got both row and column headers, but it’s worse than that because we’ve also got 3 separate header rows including merged cells (for the subjects). This is a simplified view of the data that we’re about to import into Microsoft Power BI:

You can see in the above image is that each subject teacher submits grades/scores for Current Level, Classwork and Homework.

A reminder - data in Power BI should be in flat tables with column headers

What we really want - and need - is data that looks like this:

Now we just have column headers. There are two ways we can get what we want:

1. We manually change the data in Excel after it’s been exported from the management information system. That is going to be difficult, slow and error prone. It would also need to be done each time we want to refresh our Power BI report with data from a new export. It's not a good way of doing it.

2. We ‘transform’ the data with Power Query when we do the import. The initial setup may seem more complicated, but when it is finished we have a solution that will re-run automatically whenever we refresh our data.

It's always better to do as little data manipulation as possible in MS Excel before you import your data. Let’s look at how we can use Power Query to transfrom our after it's been imported into Power BI.

Transforming our Data

1. Opening the Power Query Editor

The first thing to do is to open the Power Query editor. You can either do it when you first import the data - as part of the import process - or you can do it after the data (in its unhelpful format) is in Power BI. To do the latter, click Transform data in the ribbon menu.

2. Initial view - our imported data in its original format

This is the Power Query editor. There are three things to be aware of at this point:

1. We get a preview of our data, so we can see how it changes as we transform it with a series of steps.

2. The changes we make as we transform our data appear as a series of Applied Steps in the Query Settings pane on the right.

3. In the centre pane, above our data, is a formula or expression box. That shows you the code associated with each step. Sometimes you need to edit that directly, but in this example all the code is generated by clicking menu items in the editor.

3. Our first change - transposing the data

We’re going to make our first change to the imported data.

We’re going to transpose our table so it is turned through 90 degrees. We click Transpose in the ribbon under Transform. Our three top rows are now columns . Our first column of students has now become a series of columns - one per student.

Notice one important thing after we’ve transposed the table - we have empty null values in our first 2 columns. The next step fills in those blanks.

4. Fill Down

There’s a handy function in Power Query that will fill down empty values in a column until the next non-empty value is encountered. In this step we’re doing it twice - for those first two columns.

We select the column we want to fill and then click Fill in the Transform ribbon menu.

5. Merge Columns

This is a clever step.

We’re going to merge our first three columns - Subject, Teacher and a column of what are essentially assessment types (Class, Homework & Current Level) - into a single column. And we’re going to use an “=” sign as the delimiter.

Again the Power Query editor gives us a function. We highlight the three columns and then click Merge Columns in the ribbon under Transform.

6. Transpose again

Now we’re going to transpose again - rotating the table back again by 90 degrees so our newly merged column becomes a series of columns, one per unique value.

Notice that we now have our single student column again.

7. Promote Headers

Up until now we’ve not actually had a proper header row on our table in the Power Query editor. Look at the screenshots in the previous steps, the header row has names like ‘Column 1’, ‘Column 2’ etc.

In this step we’ll use the Use First Row as Headers button in the ribbon under Transform to ‘promote’ our first row to become the actual table headers.

8. Unpivot Columns

At the end of the last step we still had lots of columns - one per each combination of subject, teacher and assessment type. Now we're going to unpivot the data - so all those merged values that are currently column headers become values in a single column.

Now we’re going to use the super useful Unpivot Columns from the ribbon under Transform. First we need to select all the columns we want to unpivot and then we click the button.

The result is now a lot closer to what we want our finished table to look like.

9. Split Columns

This is the obvious next step. Now it’s time to split that merged column so we get separate columns for subject, teacher and type of assessment. Again the Power Query editor lets us select the column and then click Split Column.

10. Rename Columns

Another simple step. We’re going to rename the columns. You can do that directly in the editor by clicking the name you want to change.

Note that I called that column of assessment types Attribute, but it could easily have been something else (with hindsight ‘Assessment’ would have been better!).

11. Filter

We’re almost at the end. We’ve got some blank rows - or rows where there’s no grade or score in the Value column. We can filter those out. Click the arrow at the top of the column, next to the column name and uncheck the “-”.

12. Close

We’re done transforming the data. All we need to do now is Close & Apply.

A Simple Report

Back in the Power BI editor we click on the Table icon in the left hand menu to see our newly transformed data. It’s now a flat table - the best format for building reports.

And here is a simple page in the report - just two slicer visuals and a matrix visual. The matrix is a useful because it will take the Attribute column from our data and give us a column in the visual for each of the different assessment types.

Conclusion

The simplest way to get data into Power BI is through a connection to a database that’s been optimised for reporting - a data warehouse. You can pick the tables you’re interested in, import them into Power BI and use relationships to build your simple star schema.

There are times though when you need to fall back onto MS Excel - converting a .CSV file to an .XLSX, formatting as a MS Excel table and then importing into Power BI.

It’s tempting with Excel to edit the data before import. Sometimes you have to do that, but the better solution is to use Power Query to transform your data, that way you get a solution that can be used again each time you need to refresh your data.