Why Power Query Is the Excel Feature Engineers Can’t Ignore

If you find yourself buried under heaps of data with Excel as your go-to tool, then buckle up because we’re heading into the world of Power Query. Navigating through large sets of measurements or combining project timelines doesn’t have to slow you down anymore. Power Query transforms how you import, clean, and consolidate data—tasks that are all too familiar in the engineering field.

What Is Power Query?

Power Query is like a data wizard, capable of easily importing, transforming, cleaning, and consolidating data. It’s an add-in for Excel and part of the Microsoft Power BI suite, designed to simplify the process of gathering and preparing data for analysis.

Why should this matter to you? Because in the engineering world, data is king. You deal with vast amounts of it daily – from measurements and simulations to project timelines and budget figures. Power Query can help you manage this data more efficiently, saving you time and reducing errors.

Why Engineers Should Care About Power Query

1. Automates Repetitive Tasks: You likely find yourself performing the same data manipulation tasks over and over again. Power Query can automate these tasks, allowing you to focus on more complex analysis and decision-making.

2. Handles Large Data Sets with Ease: Engineering projects often involve large sets of data that can be cumbersome to manage in standard Excel sheets. Power Query excels at processing large volumes of data without slowing down your computer.

3. Improves Data Accuracy: By automating data preparation tasks, Power Query reduces the risk of human error, ensuring that your analyses are based on accurate information.

4. Saves Time: Time is a precious commodity in engineering. By streamlining the process of importing and cleaning data, Power Query frees up your schedule for other important tasks.

Getting Started with Power Query in Excel

Accessing Power Query

To get started with Power Query in Excel, you first need to know where to find it. If you’re using Excel 2016 or later versions (including Office 365), congratulations! Power Query is built-in and located under the “Data” tab in the ribbon, labeled as “Get & Transform Data.”

For those using older versions of Excel (2010 or 2013), you’ll need to download the Power Query add-in from Microsoft’s website. Once installed, it will appear as a new tab in your Excel ribbon.

Basic Interface Overview

When you launch Power Query Editor by selecting “Get Data” from the “Data” tab, you’re greeted by a user-friendly interface divided into several key areas:

  • The ribbon at the top contains tools for managing queries.
  • The left pane displays a list of queries currently loaded into your workbook.
  • The central pane shows a preview of the selected query’s data.
  • The right pane contains query settings and applied steps.

Navigating through these areas becomes intuitive once you start exploring them with your own data.

The Core Features of Power Query

Importing Data from Various Sources

One of Power Query’s most powerful features is its ability to import data from a wide range of sources including files (Excel, CSV, XML), databases (SQL Server, Oracle), web pages, and even cloud-based services like SharePoint. This versatility makes it an invaluable tool for engineers who work with diverse datasets.

To import data into Excel using Power Query:

  1. Go to the “Data” tab.
  2. Click “Get Data”.
  3. Choose your source (e.g., “From File”, “From Database”).
  4. Follow the prompts to connect to your data source and load your data.

Data Transformation and Cleaning

After importing your data into Power Query Editor, you’ll likely need to clean or transform it before analysis. This could include removing duplicates or irrelevant columns, filtering rows based on certain criteria, or merging columns.

Powerful transformations can be applied directly from the ribbon menu:

  • Use “Remove Columns” or “Remove Rows” to eliminate unnecessary parts.
  • Apply filters by clicking on column headers.
  • Combine columns using formulas.

Combining Data from Multiple Sources

Another standout feature is combining datasets from different sources – crucial when working on complex engineering projects that require integrating various types of information.

You can merge queries by:

  1. Going to Home > Merge Queries.
  2. Selecting two tables and specifying how they should be joined (e.g., inner join).
  3. Choosing which columns will serve as keys for merging.

Or append queries by:

  1. Going to Home > Append Queries.
  2. Selecting two or more tables that will be combined into one table vertically.

Real-World Applications of Power Query for Engineers

Example 1: Automating Repetitive Data Cleaning Tasks

Imagine receiving weekly reports containing raw readings from multiple sensors that need cleaning before analysis—removing outliers or converting units might be necessary steps each time.

With Power Query:

1) You set up these steps once.

2) Each week when new reports arrive; simply refresh your query.

3) Watch as all cleaning steps are automatically applied!

This not only saves hours but also ensures consistency across analyses.

Example 2: Merging Data from Different Projects

Often engineers work on projects that span different departments or even organizations resulting in disparate datasets needing consolidation—budget figures from finance combined with project timelines from planning departments for comprehensive cost-time optimization analyses.

By utilizing merge functionalities within Power Query you seamlessly integrate these varied datasets to enable holistic project assessments previously bogged down by manual consolidation efforts.

Example 3: Complex Data Manipulations for Analysis

Consider needing advanced calculations such as weighted averages across grouped categories within extensive datasets—a common requirement when analyzing material efficiency.

Using group-by features alongside custom column creation capabilities within power query
enables you to execute complex manipulations effortlessly. Gaining deeper insights into material usage patterns can highlight significant cost savings opportunities.

Explore Power Query Further with EngineerExcel Academy

This blog post introduces you to the essentials of Power Query, based on a comprehensive new course available inside EngineerExcel Academy. For more in-depth tutorials, examples, and step-by-step guides on leveraging Power Query for your engineering projects, we invite you to learn more and join us at EngineerExcel Academy. Take advantage of this opportunity to enhance your Excel skills and elevate your engineering analyses!

Scroll to Top
Complete... 50%
Please enter your name and email address below to receive a link to the toolkit.

You’ll also receive regular tips to help you master Excel for engineering.



By Charlie Young, P.E.

Take your engineering to the next level with advanced Excel skills.