Using appending in Power Query offers a streamlined approach to automatically grade student exams based on predefined thresholds. This method eliminates manual grading errors and saves significant time when processing large sets of student scores, providing an efficient way to manage academic assessments.
The technique combines student scores with grade thresholds to generate accurate results that can be easily updated when grading criteria change.
Watch my step-by-step tutorial on how to implement this grading system using Power Query:
Understanding the Grading Process with Power Query
Power Query’s appending feature allows us to combine multiple tables in a way that creates relationships between student scores and grade thresholds. The appending process essentially stacks tables on top of each other, creating a single dataset that we can manipulate to automatically assign grades. This approach is particularly useful in educational settings where instructors need to process multiple exam results efficiently.
The primary advantage of using this method is that when grading criteria change, you don’t need to manually reassign grades to each student. Simply update the threshold values in your source table, refresh the query, and all grades will automatically update to reflect the new standards.
Required Resources
Before starting the grading process, you’ll need two essential tables:
A table containing student names and their exam points
A table defining grade thresholds (what point ranges correspond to which letter grades)
These tables will serve as the foundation for our automated grading system. The structure of these tables doesn’t need to be complex—just clearly organized with appropriate headers to identify the data.
Setting Up the Tables in Power Query
To begin implementing the grading system, we need to import both tables into Power Query. This process allows us to manipulate and combine the data efficiently.
First, with the student scores table selected, navigate to the Data tab in Excel. From there, select “From Table/Range” to import the first table into Power Query. When prompted to load the data, choose “Create Connection Only” as we’ll be manipulating this data further before bringing it back to Excel.
Repeat the same process for your grade thresholds table. Having both tables accessible in Power Query is essential for the next step where we’ll combine them through appending.
Appending the Tables
Once both tables are in Power Query, we can combine them using the append function:
On the Home tab within Power Query Editor, click the Append Queries button (not “Append Queries as New”). In the dialog box that appears, confirm that you want to append just the two tables you’ve imported. Select the student table as your primary table and the grades threshold table as the table to append.
After appending, you’ll notice that the resulting table contains all rows from both original tables. The student data appears first, followed by the threshold data. This combined dataset gives us everything we need to assign grades automatically.
Organizing and Sorting the Data
For our grading system to work properly, we need to sort the appended data by the number of points. This sorting is a crucial step because it allows us to arrange scores in ascending order, making it easier to assign appropriate grades.
Click on the column containing the points/scores and sort from A to Z (ascending). This action arranges all scores from lowest to highest, with the grade thresholds integrated among the student scores based on their point values.
Because of how the data is now arranged, the grade thresholds appear at positions where they naturally divide the student scores into different grade categories.
Assigning Grades Automatically
With our data properly sorted, we can now use a simple but powerful technique to assign grades to each student. The key to this process is the Fill Down command in Power Query.
The grade column initially has values only in the rows that came from our threshold table. To assign these grades to students, select the grade column and navigate to the Transform tab. Click on “Fill” and then “Down.” This action copies each grade down to all rows below it until another grade is encountered.
As a result, each student row is automatically assigned the grade corresponding to their score based on the defined thresholds. For example, if the threshold for grade C is 40 points, students with 40 or more points (but below the next threshold) will be assigned a C.
Filtering and Finalizing the Results
After filling down the grades, we need to filter out the threshold rows to see only the student results:
Click the filter button on the column containing student names and select “Filter out empty values” or specifically select only the rows containing actual student data. This leaves us with just the student information and their corresponding grades.
With the data filtered and grades assigned, we can now load this information back to Excel. Navigate to the Home tab and select “Close & Load To.” Choose where you want to place the results in your workbook.
Updating Grades When Thresholds Change
One of the most powerful aspects of this approach is how easily you can adjust grading criteria:
If you need to change the grading thresholds, simply update the values in your original threshold table. For example, you might change the minimum score for an A from 90 to 88 points. After making these changes, save your file and then refresh the query (right-click on your results table and select “Refresh”).
The entire grading process will automatically run again with the new thresholds, updating all student grades accordingly. This makes the system extremely flexible and saves significant time when adjustments are needed.
Real-World Application Example
In my video demonstration, I showed how this system works with a sample dataset. For instance, Jennifer earned 13 points which resulted in an F grade, Robert received 34 points also resulting in an F, while Olivia scored 40 points which met the threshold for an E grade.
After changing the thresholds and refreshing the query, the grades updated automatically to reflect the new standards. This dynamic updating capability is what makes this approach so valuable for educators who may need to adjust grading scales based on class performance or other factors.
Power Query offers powerful solutions for cleaning data across columns by leveraging information contained in related columns. In this article, I’ll demonstrate how to efficiently remove cryptocurrency symbols from a name column when those symbols already exist in a separate column, even when the symbols have varying lengths.
This technique is particularly valuable when dealing with inconsistent data formats that require standardization for analysis.
Watch my step-by-step tutorial video to see these techniques in action:
Understanding the Data Cleaning Challenge
When working with financial data, especially cryptocurrency information, you’ll often encounter datasets where the same information appears redundantly across multiple columns. In my example, I have a table containing cryptocurrency data where the name column includes both the cryptocurrency name and its symbol (like “Bitcoin BTC”), while a separate symbol column already contains just the symbol (“BTC”).
The challenge lies in cleaning the name column by removing these redundant symbols. What makes this particularly tricky is that cryptocurrency symbols vary in length — some might be three characters (BTC), others four (DOGE) or more. This inconsistency means we can’t simply remove a fixed number of characters from the end of each name.
Preparing the Data in Power Query
To begin the cleaning process, we need to load our data into Power Query where we can apply the necessary transformations:
Navigate to the Data tab in Excel
Select your data range and choose to load it to Power Query
Remove any unnecessary columns to focus on the task at hand
In my case, I initially removed several columns to focus on just the key columns for this demonstration — the name column containing both cryptocurrency names and symbols, and the symbol column containing just the symbols. This simplified approach allows us to concentrate specifically on the cleaning technique without distraction.
Solution #1: Using Text After Delimiter
The first solution uses Power Query’s built-in functions to extract the relevant part of the text. This approach is more straightforward and uses the UI-based options:
To implement this solution:
Select the name column in Power Query
Go to the Add Column tab
Choose Extract and then Text After Delimiter
Initially, when using the UI, you might try entering a specific symbol like “BTC” as the delimiter. However, this would only work for one cryptocurrency. The real power comes from modifying the formula to use the symbol column as a reference.
After creating the initial formula, you’ll need to edit it in the formula bar to make it dynamic. Replace the hardcoded delimiter text with a reference to the symbol column. This way, Power Query will use each row’s corresponding symbol to extract just the cryptocurrency name, regardless of the symbol’s length.
Solution #2: Using Text.RemoveRange Function
The second solution is more complex but demonstrates the flexibility of Power Query’s M language. For this approach:
Go to the Add Column tab
Select Custom Column
Use the Text.RemoveRange function to precisely remove the symbol part
This function works by removing a specific range of characters from the text. It requires three parameters:
The source text (our name column)
The starting position (0‑based in Power Query)
The number of characters to remove (which we determine using the length of the symbol)
Remember that Power Query uses zero-based indexing, meaning the first character position is 0, not 1. This is crucial when working with text manipulation functions.
Finalizing the Transformation
After applying either solution, we need to clean up our query:
Rename the newly created column to something meaningful (like “name”)
Delete the original name column that contained the symbols
If needed, restore any previously removed columns by deleting the “Removed Columns” step
Arrange columns in the desired order by dragging them
In my example, I also demonstrated how to clean another column (circulating supply) that had a similar issue but with a simpler pattern. Since all the unwanted symbols in this column were preceded by a space, I could use the Extract Text Before Delimiter function with a space as the delimiter.
Loading the Cleaned Data Back to Excel
Once all transformations are complete, we can load the cleaned data back to Excel:
Go to the Home tab in Power Query
Select Close & Load To
Choose whether to load to a new worksheet or an existing location
In my demonstration, I loaded the data to cell A13 on the existing worksheet, which allows for easy comparison between the original and cleaned data.
Why This Approach Matters
The beauty of using Power Query for this type of data cleaning is that it creates a repeatable process. If your data source updates, you can simply refresh the query, and all the cleaning steps will be reapplied automatically. This saves significant time compared to manual cleaning or using formulas that would need to be copied down for each new set of data.
Additionally, by leveraging information from one column to clean another, we create a more robust solution that can handle variations in the data format without breaking. This approach is far superior to hard-coding specific text values or character counts that might change across different datasets.
In this guide, I’ll show you how to automatically remove completely empty columns in your Excel data using Power Query. This powerful technique saves you from tedious manual deletion and works even when columns contain null values, empty cells, and empty text strings (represented by “”).
The approach I’ll demonstrate uses a specific Power Query formula that identifies and removes columns with no meaningful data, greatly improving your data cleaning workflow.
Watch my detailed video walkthrough of this process:
Understanding Empty Columns in Power Query
When working with data in Excel, we often encounter columns that appear empty but can actually contain different types of “emptiness” that Power Query recognizes differently. It’s important to understand that Power Query distinguishes between null values (truly empty cells), and cells containing empty text strings (“”).
An empty text string occurs when a cell contains a formula that returns “” or when you paste such values. These look empty visually in Excel, but Power Query treats them differently than cells with null values. Our goal is to create a solution that identifies and removes columns regardless of which type of “emptiness” they contain, as long as the entire column contains no meaningful data.
Importing Data into Power Query
To begin the process of removing empty columns, we first need to import our data into Power Query. Here’s example how to do this:
Go to the Data tab in Excel
Click on “Get Data” → “From File” → “From Excel”
Locate and select your Excel file
Choose the worksheet containing your data (in my example, Sheet1)
After importing, you’ll notice that some columns might appear empty but contain different types of emptiness — some cells might have null values (shown as “null” in Power Query), while others might be truly empty or contain empty text strings. Our code will handle all these scenarios automatically.
The Power Query Solution
Rather than manually deleting empty columns, we’ll use a custom formula in Power Query that automatically identifies and removes all empty columns. This approach is much more efficient, especially when working with large datasets.
The Complete Code
Here’s the complete Power Query formula that we’ll use to remove empty columns:
To use this code in your own projects, you’ll need to replace #“Change Type” with the name of your previous step in Power Query. This reference appears in three places in the formula, so be sure to update all instances.
Breaking Down the Formula
Let’s break down this formula to understand how it works:
The formula starts with Table.RemoveColumns(), which is the function that will eventually remove our empty columns from the table. This function takes two parameters: the table we’re working with, and a list of column names to remove.
To generate the list of column names to remove, we use List.Select(), which filters a list based on a condition. We’re filtering the list of all column names (obtained using Table.ColumnNames()) to find those that meet our “empty column” criteria.
Extract all values in the column using Table.Column()
Remove any values that are empty strings or null using List.RemoveMatchingItems()
Count how many items remain using List.Count()
Check if this count equals zero (meaning the column is completely empty)
If the count equals zero, the column is considered empty and will be included in our list of columns to remove.
Implementing the Solution
To implement this solution in your Power Query Editor:
Click “Add Step” to create a new transformation step
In the formula bar, paste the complete code shown above
Replace “#“Change Type”” with the name of your previous step (typically the step after promoting headers)
Press Enter to apply the transformation
You should now see your data with all empty columns removed. The transformation happens immediately, leaving only columns that contain at least one non-empty value.
Testing the Solution
To verify that our solution works correctly, we can test it with different types of data. In my video, I demonstrated this by:
1. Starting with a dataset that contained several empty columns (with various types of emptiness)
2. Applying our Power Query solution to remove these columns
3. Modifying the source data to add a new column with a single value
4. Refreshing the query to verify that the column with a value is preserved
The solution correctly identified and retained columns that had at least one meaningful value, while removing columns that were completely empty.
Advanced Usage and Adaptations
This solution can be easily adapted to work with different datasets. The key is to ensure you’re referencing the correct previous step in your Power Query transformation sequence. If your previous step is named differently, simply replace all instances of “#“Change Type”” with the appropriate step name.
When working with very large datasets, this automated approach can save significant time compared to manually identifying and removing empty columns. Additionally, the solution is dynamic — if your source data changes and a previously empty column now contains values, refreshing the query will automatically retain that column.
Loading the Results
After applying our transformation, we can load the results back to Excel:
Click “Close & Load” (or “Close & Load To…” if you want to specify a destination)
Choose your preferred destination (worksheet, data model, etc.)
The cleaned data, with empty columns removed, will be loaded to your specified location
The resulting table will contain only meaningful columns that have at least one value. This makes your data more concise and easier to work with.
When your source data changes, simply refresh the query to apply the same transformations to the updated data. The empty column removal logic will be reapplied automatically, adapting to any changes in your source data’s structure.
