Excel Power Query
What is Power Query?
Power Query is a tool that helps you collect data from different places, clean it up, and organize it the way you need. You can do this using a simple editor without writing code, and then load the data into Excel, Power BI, or other tools.
Where to find it in Excel?
• Open Excel
• Click on the Data at the Top Ribbon
• Click on Get Data (Power Query) to load data from different sources
Common supported data sources in Power Query
• Excel Workbooks
• CSV files
• SQL Server
• SharePoint
• Azure and AWS data storage
This is what the Power Query window looks like after loading your data:
Types of transformations in Power Query
1. Row Transformation: Changes applied to existing data rows. Example: filtering rows, removing duplicates, sorting, replacing values.
2. Column Transformations: Creates or modifies columns in your table. Example: adding a calculated column, splitting columns, extracting values from a column.
We will be working with this table to perform data transformations using Power Query.
To follow along, download power_query_data.xlsx. It has each table used on this page on its own worksheet, set up as an Excel Table: customers, states, customers_nested and population. (The screenshots call these Table1, Table2 and Sheet1.)
Excluding rows: Filter
• Select the column you want to filter on
• Go to the column header and click the drop-down filter icon
• Choose the values or set conditions (e.g., “Equals”, “Greater Than”, etc.)
• Power Query will keep only the rows that meet your filter criteria
The Query Settings panel (on the right side) shows the name of your query and keeps track of every step you take while cleaning your data. It’s like a timeline of your work, easy to follow, easy to fix, and super helpful when you want to reuse or update your process later.
Excluding columns: Remove Other Columns
• Go to the Home tab
• Select the columns you want to keep
• Right-click on a column header and choose Remove other columns
• Power Query will display only the selected columns
New columns: Custom Column
• Go to the Add Column tab
• Click on Custom Column
• In the formula editor, enter your calculation (e.g., if [customer_age] > 60 then “Senior” else “Adult”)
• Give the new column a name such as AgeGroup and click OK
• Power Query will add this new calculated column to your table
Extract data from cells: Split Column
• Select the column you want to split (e.g., customer_name)
• Go to the Home tab, click Split Column, and choose By Delimiter (for example, a space ” “)
• Choose whether to split into separate columns or into rows
• Power Query will split names like “Jihong Li” into two columns: Jihong and Li
Treating cases differently: Conditional Column
• Select the column you want to base your rule on (e.g., customer_age)
• Go to the Add Column tab and click Conditional Column
• Define your rules (e.g., If [customer_age] ≥ 60 then “Senior” else “Adult”). Click OK
• Power Query will create a new column showing whether each customer is a Senior or Adult
Recode/lookup: Replace Values or Merge Queries
Recode
• Select the column where you want to change values
• Go to Transform → Replace Values
• Type in the old value and the new value
• Power Query swaps the old value with the new one inside that column
• Repeat these steps for each value you want to recode
Lookup
• Start with your main table (Table1, customers in the download) that has customer details like customer_name, customer_age, customer_state
• Load the second table (Table2, states in the download), which has State and Abbreviation values
• Go to the Home tab, click Combine, and choose Merge Queries
• In the Merge dialog, select customer_state from Table1 and State from Table2 as the matching columns
• Choose the join type and click OK
• A new column with [Table] values appears — click the expand icon (two arrows) on top of this column
• Select the columns you want to bring in from Table2 (e.g., Abbreviation) and click OK
• Power Query expands the data, showing the new Abbreviation column alongside your original table
Un-nest: Split Column into Rows
• Select the column you want to separate (e.g., customer_name if it contains values like “Jihong Li, Harry, Ron”)
• Go to the Home tab, click Split Column, choose By Delimiter, then select Advanced Options
• In the dialog, choose Split into Rows instead of columns
• Power Query will expand the value “Jihong Li, Harry, Ron” into three rows
Group aggregate: Group By
• Go to the Home tab and click Group By
• Choose the column you want to group on (e.g., customer_state)
• In the Operations box, pick the aggregation you need (e.g., Count of customer_name)
• Power Query will return one row per state, showing how many customers belong to each state
Longer and wider: Unpivot and Pivot Column
Unpivot (Wide → Long):
• Select the identifier column you want to keep (e.g., country)
• Go to the Transform tab, click Unpivot Columns, and choose Unpivot Only Selected Columns (e.g., 2010, 2011, 2012, 2013)
• Power Query will create two new columns: Attribute (Year) and Value (Population)
Pivot (Long → Wide):
• Pick the column whose values you want to turn into headers (for example, Year)
• Go to the Transform tab and choose Pivot Column
• Select the column that has the numbers or data you want to fill in (for example, Population)
• Power Query will rearrange the table into wide format, creating separate columns like 2010, 2011, 2012, 2013
Advantages and disadvantages of Power Query
The main difference is that pandas, R, and SQL need you to write code; Power Query lets you do the same transformations just by clicking and selecting options. It keeps track of all the steps for you, so you don’t have to remember commands. Since it’s built right into Excel, it’s very handy for quickly cleaning and organizing data; it is also used in other Microsoft products, such as Power BI and the SharePoint ecosystem.
Underneath it does write code (in a language called M), although my sense is that people rarely examine that underlying code.
A downside of Power Query is that it is harder to share and collaborate using version control like git or github.
Excel quick reference
| Transform | Worksheet | Power Query |
|---|---|---|
| Exclude rows | Filter | Filter (column drop-down) |
| Exclude columns | Delete columns | Remove Other Columns |
| Sort | Sort | Sort (column drop-down) |
| New columns | Formula, copied down | Custom Column |
| Extract data from cells | Split Column (By Delimiter) | |
| Treating cases differently | Conditional Column | |
| Recode/lookup | XLOOKUP | Replace Values or Merge Queries |
| Un-nest | Split Column (into Rows) | |
| Aggregate | SUM | |
| Group Aggregate | Pivot Table | Group By |
| Longer | Unpivot Columns | |
| Wider | Pivot Table | Pivot Column |