Automating Routine Excel Tasks Using Power Query

Bottom Line: Learn how to automate 5 routine Excel tasks using Power Query.

Skill level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can use the same workbook that I use in the video by downloading this file:

Power Query Magic

If you use Excel, then you've probably heard the buzz around Power Query, and rightly so.

This exciting feature can transform the way you handle data, automating complex tasks with just a few clicks.

In this post and video, we take a look at how to automate five common Excel tasks with Power Query. These are tasks that can be boring, tedious, and time consuming. In other words, the stuff that no one likes to do.

1. Automating Data Cleanup with Power Query

Let's kick off our journey by tackling a common data preparation process. Imagine receiving a daily CSV file from a vendor that needs cleaning up.

This includes removing columns, deleting blank rows, and sorting the data. With Power Query, this task becomes a breeze.

1. Navigate to the Data tab on the ribbon.
2. In the “Get & Transform Data” section, click on “From Text/CSV.”
3. Select the folder containing your CSV file and click Import.
4. Once in the Power Query Editor, remove unnecessary columns, eliminate blank rows, and sort data as needed.
5. With your data now refined, go to the Home tab and click “Close & Load” to create a new sheet with your cleaned data.

This process not only saves time but sets the stage for a seamless daily routine. Simply replace the file with the new data, refresh the query, and let Power Query do the heavy lifting for you.

2. Effortless Combination of CSV Files

In this next task, we'll use Power Query to combine CSV files. Let's imagine we have 11 CSV files from each store location, and we're tired of manually copying and pasting data.

Power Query to the rescue!

1. Navigate to the Data tab.
2. Choose “Get Data” and then “From Folder.”
3. Browse to the folder containing your CSV files and click Open.
4. Combine and transform the data by selecting “Combine & Load” in the preview window.
5. Review the combined data in the Power Query Editor, and if needed, perform additional transformations.
6. Click “Close & Load” on the Home tab to create a new sheet with your consolidated data.

We can also drop new files into the folder and simply refresh the query to add the new data to the output table. This fully automates data combining tasks with just a few clicks.

3. Parsing and Analyzing Comma-Separated Data

For our third task, we'll help out the HR department. They want to analyze how many employees attended each event, and they recorded attendance using comma-separated tags in a column.

Power Query provides a simple way to parse these tags for easier analysis.

1. Navigate to the Data tab.
2. If your data is already in an Excel table, select “From Table/Range.”
3. In the Power Query Editor, choose the tags column.
4. Utilize the “Split Column” feature on the Home tab, selecting “By Delimiter” and specifying the delimiter as a comma followed by a space.
5. Choose “Split Into Rows” under Advanced Options.
6. A new row is created for each tag.

This layout creates duplicates in the other columns, but this is ok. It makes it easier to count the rows for each tag using the Group By feature.

1. Group the data by going to the Home tab, clicking “Group By,” and selecting the desired grouping options.
2. Click “Close & Load” to generate a new sheet with the summarized report.

With tags neatly parsed, the HR department can now easily analyze attendance data. Power Query's ability to split and transform data offers a dynamic approach to handling intricate datasets.

4. Replacing XLOOKUP with Power Query

Next, we'll assist the sales department by using Power Query to perform a lookup instead of choosing more traditional functions like VLOOKUP or XLOOKUP.

The objective is to incorporate representative names (rep names) from the reps table into the existing sales data table based on the matching rep ID column.

1. Load your relevant tables into Power Query.
   
   • For the lookup table, select “Create Connection” on the Close & Load drop-down.
   • For the main table, choose “From Table/Range.”
2. Merge the tables using the “Merge Queries” feature on the Home tab.
3. Select the columns for matching, create a Left Outer Join, and click “OK.”
4. Expand and choose the desired columns.
5. Click “Close & Load” to create a new sheet with the merged data.

This automated process eliminates the need for lookup formulas that are, let's be honest, prone to breaking.

Plus, updating the data source with new information is easy – simply add the new data, right-click, and refresh the query to seamlessly incorporate the latest information.

Watch the video tutorial on YouTube to uncover this hidden gem and elevate your Excel skills.

5. Transforming Report-Style Data for Analysis

And finally, we're going to help out the marketing department by using one of my favorite Power Query features. They want to analyze data in more detail, but in its current report-style layout, the process is difficult and time-consuming.

So let's transform report-style data with Power Query!

1. Navigate to the Data tab.
2. Choose “From Table/Range” and select the data with the month-wise columns.
3. Select the month columns and pivot them using the “Pivot Columns” feature on the Transform tab.
4. Rename the columns as needed.
5. Click “Close & Load” to create a new sheet with the transformed data.
6. This reshaped layout facilitates more insightful analysis using pivot tables or formulas.

With the data now in a more manageable format, the marketing department can use Excel's analytical tools (like pivot tables and formulas) for further analysis.

Conclusion

Power Query isn't just a tool; it's a game-changer in the world of Excel.

Not only does it save time, but Power Query also helps reduce errors and discover new insights about your data. In short, it will make you look like an Excel rockstar.

If you found this guide helpful, leave a comment below and share your Power Query success stories!