Power Query: Joining Grades

Welcome to our Power Query blog. This week, I look at last week’s problem again, but this time I translate numbers to letters in multiple columns by joining tables.

I (still) have the exam results for some children who were lucky(!) enough to take their exams when the system was in the middle of changing from letters to numbers. I want to replace the numbers with text so that I can see everything in the same format – this time I have two tables – I have created a table of the current numeric grades and their letter equivalent:

I create a query ‘From Table’ in the ‘Get & Transform’ section of the ‘Data’ tab. I begin by creating a query for my smaller numeric and letter equivalents table.

On the ‘Close & Load’ dropdown I choose to ‘Close & Load To…’ so that I can create a ‘Connection Only’ query for this table, viz.

I can now create a query for my list of grades.

From within my ‘Grades List’ query, I can choose to ‘Merge Queries’ from the ‘Combine’ section of the ‘Home’ tab.

I have chosen a ‘Left Outer’ join as I want to keep my original ‘Grades List’ query and add the letter equivalents. I join the English Language and the Number Grade columns.

The merge has given me the letter equivalents for my English Language column: the first entry shows that the first grade in the table 8 corresponds to A*/A. I can expand the Grade Equivalents column.

I only want the letter grade, so I expand the column and rename the letter grade to show that it corresponds to the English Language column.

Now I have my Eng Lang Letter Grade column, I repeat the merge process for the English Language and Mathematics columns.

I have all my equivalent grades. I ‘Close & Load’ my query to a new worksheet.

I can also handle the issue of the examination board adding a level 10 to the grades and altering the boundaries.

If I update, my ‘Grades List’ query is refreshed, and I see that the grades equivalents have changed for 9, 8 and 7.

There is yet another way to approach this task, and I’ll look at another method next time which combines replacing data with using the equivalents table.

Come back next time for more ways to use Power Query!