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.

Raw CSV File for Data Cleanup with Power Query

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.
Delete Blank Rows in Excel with Power Query

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.

Power Query Output Table in Excel after Data Preparation

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.

Combine CSV Files in Excel with Power Query

Power Query to the rescue!

Combine and Transform Button in Power Query Excel
  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.
Power Query Output Table of CSV Files Combined Appended in Excel

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.

HR Data for Analysis with Power Query in Excel

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

Power Query Split Data by Delimiter into Rows in Excel
  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.
Power Query Split Data by Delimiter into Rows - Result in Excel

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.

Group By Feature in Power Query to Create Summary Report
  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.
Power Query Output Table in Excel after Split and Group By

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.

Perform a VLOOKUP in Power Query with Merge

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.

Replace XLOOUP in Excel with Merge in Power Query
  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.
New Columns Expanded from Lookup in Power Query

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.

Report Style Layout in Excel Transform for Data Analysis - Unpivot

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.
Unpivot Columns Button Transforms Data for Analysis in Power Query Excel

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.


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!


Your email address will not be published. Required fields are marked *

  • Somehow, I always found PowerQuery somewhat scary. This vid made it very clear.
    Thank you so much.
    I am seriously considering buying the Access course.
    When will XL be updated to show the checkbox feature in the insert tab?
    Thank you

      • Thank you for your reply. I have Chrome but its not responding to recognize the link as a downloading link, but much rather like if it were a background type of picture I guess is how best I can describe it, where no matter how many times I click on it nothing happens 🙁

          • That’s great to hear, Angel! Would you mind sharing how you got it to work? Just so we can investigate if it is an issue we can prevent on our end. Thanks!

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter