Power Query Basics

Importing Data into Power Query

Power Query is Excel's built-in ETL tool. It connects to data sources, applies a sequence of transformation steps, and loads the result into a worksheet or the Data Model. Every transformation is recorded as a step that can be replayed, modified, or deleted. When the source data changes, you refresh the query and every step re-executes automatically.

Opening Power Query

Go to Data > Get Data. The menu lists source types: From File (Excel, CSV, XML, JSON), From Database (SQL Server, Access, etc.), From Azure, From Online Services, and From Other Sources. For a CSV file, choose From File > From CSV. For an Excel workbook, choose From File > From Excel Workbook.

After selecting the source, the Power Query Editor opens. This is a separate window that shows a preview of the data and a pane on the right called "Query Settings" with an "Applied Steps" section. Every transformation you apply is recorded here as a named step.

Importing a CSV

Suppose you have a monthly sales export: sales_2026_01.csv. Go to Data > Get Data > From File > From CSV. Navigate to the file and click Import. Power Query reads the file and shows a preview.

The first thing to check is the column types. Power Query guesses types based on the first 2,000 rows. If a column looks numeric in the preview but contains text further down, the type detection will fail for those rows. You will see Error values in the preview. Fix this by explicitly setting the column type: click the icon in the column header and choose the correct type (Text, Whole Number, Decimal Number, Date, etc.).

Importing from Excel

To import from another Excel workbook, go to Data > Get Data > From File > From Excel Workbook. Select the file and a Navigator pane appears listing all sheets and tables in the workbook. Select the sheet you want and click Transform Data (not Load — Load skips the Power Query Editor).

Importing from a Folder

The folder import is the most powerful pattern for recurring reports. Suppose you receive a new CSV every month: sales_2026_01.csv, sales_2026_02.csv, etc. Instead of importing each file individually, put them all in a folder and import the folder.

Go to Data > Get Data > From File > From Folder. Select the folder and click Import. Power Query shows a list of all files in the folder. Click "Combine & Transform Data" and Power Query uses the first file as a template, then applies the same transformations to every file in the folder. The result is a single table containing all rows from all files.

When a new file arrives, drop it into the folder and refresh the query. The new data is automatically included. This is the core value proposition of Power Query: a repeatable import pipeline that eliminates manual copy-paste.

Query Settings and Applied Steps

The Applied Steps pane on the right lists every transformation in order. Each step is a line of M code (Power Query's formula language). You do not need to write M code — the UI generates it — but you can click the gear icon on any step to modify its parameters, or click the X to delete a step.

The order of steps matters. Power Query executes them sequentially. If you filter rows before removing columns, the filter applies to all columns. If you remove columns first, the filter applies only to the remaining columns. Rearrange steps by dragging them in the Applied Steps pane.

Transformation Steps for Cleaning and Shaping

Most raw exports need cleaning before they are usable. Power Query provides a set of transformation operations that cover the common cases: removing columns, filtering rows, splitting columns, changing types, and handling errors.

Removing Columns

Select a column and press Delete, or right-click > Remove. To remove multiple columns, Ctrl+click the headers and delete them together. To keep only specific columns, use Home > Choose Columns and select the ones you want.

Removing columns early reduces the data volume for subsequent steps, which improves performance. If you know you will never use a column, remove it first.

Filtering Rows

Click the dropdown arrow in a column header and select the values to keep. For numeric columns, you get filter options like "Equals," "Greater Than," "Does Not Equal." For text columns, you get a checkbox list of unique values.

Filtering is row-level. Each filter condition removes rows that do not meet it. Multiple filters combine with AND logic: a row must pass all filters to be kept.

Splitting Columns

A common problem: a column contains combined values, like "West - Widget" that should be two columns: Region and Product. Select the column, go to Transform > Split Column > By Delimiter. Choose the delimiter (hyphen, comma, space, etc.) and specify whether to split at the first occurrence, the last occurrence, or every occurrence.

Power Query also splits by position (e.g., first 3 characters) and by number of characters. Use these for fixed-width formats like account codes.

Changing Column Types

Click the icon in the column header to set the type. The options are Text, Whole Number, Decimal Number, Date, Date/Time, Time, Duration, and Logical (TRUE/FALSE).

If a column contains mixed types — numbers and text — changing the type will produce Error values for the text entries. To handle this, use Transform > Replace Errors to substitute a fallback value (like 0 or "Unknown") for the errors. Alternatively, use Transform > Detect Data Type to let Power Query infer types from the full dataset, not just the preview.

Handling Nulls and Errors

Null values (Power Query's equivalent of blank cells) and error values are the two most common data quality issues in raw exports.

To replace nulls: select the column, go to Transform > Replace Values, leave the "Value To Find" field empty (or enter null), and enter the replacement value. For numeric columns, replace nulls with 0. For text columns, replace with "Unknown" or an empty string.

To replace errors: select the column, go to Transform > Replace Errors, and enter the fallback value. This is essential when a column type change produces errors for non-conforming rows.

To remove rows with nulls or errors: filter the column to exclude nulls, or use Home > Remove Rows > Remove Blank Rows / Remove Errors.

Adding Custom Columns

Sometimes the source data lacks a column you need. Use Add Column > Custom Column to create one with a formula. The formula syntax is Power Query's M language, but for simple expressions it is intuitive:

[Revenue] - [Cost]

This creates a Profit column. You can reference other columns by name in square brackets. For conditional logic, use the if syntax:

if [Region] = "West" then "Western" else "Other"

Pivoting and Unpivoting

Pivoting transforms unique values from a column into new columns. If you have a column Month with values Jan, Feb, Mar and a column Revenue, pivoting on Month creates three columns: Jan, Feb, Mar, with revenue values in the appropriate column.

Unpivoting does the reverse: it converts columns into rows. If you have columns Jan, Feb, Mar with revenue values, unpivoting creates two columns: Month (containing Jan, Feb, Mar) and Revenue (containing the values). Unpivoting is one of the most common transformations because raw exports often have a wide format that needs to be normalized into a tall format for analysis.

Building a Repeatable Refresh Pipeline

The core value of Power Query is repeatability. You build the transformation pipeline once, and it replays every time the source data changes. This chapter covers the patterns that make a pipeline truly repeatable.

The Refresh Discipline

After building your query, load it into a worksheet (Home > Close & Load). The result is an Excel Table containing the transformed data. When the source file changes — new rows added, values corrected — you refresh the query:

• Right-click the output table > Refresh.
• Data > Refresh All.
• Data > Queries & Connections > right-click the query > Refresh.

Every applied step re-executes against the current source data. The output table updates in place. No manual rework is required.

Parameterizing the Source

A common pattern: you have a query that imports sales_2026_01.csv, and next month you need it to import sales_2026_02.csv. Instead of editing the query, parameterize the file path.

1. Go to Home > Manage Parameters > New Parameter.
2. Name it FilePath, set the type to Text, and enter the current file path.
3. Edit the query's source step to reference the parameter instead of a hardcoded path.

When the new file arrives, update the parameter value and refresh. The query reads the new file and applies all transformations.

Combining Multiple Files

The folder import pattern (covered in the previous chapter) is the most scalable approach for recurring reports. But there is a subtlety: the files must have the same structure. If the January file has columns A, B, C and the February file has columns A, B, C, D, the combine operation will fail or produce inconsistent results.

To handle schema changes, use the "Combine Files" option in the folder import, which uses the first file as a template. If a new file has an extra column, Power Query adds it to the output with null values for files that lack it. If a new file is missing a column, Power Query fills the missing values with nulls.

For more control, you can write a custom function that applies transformations to each file individually, then invoke that function on every file in the folder. This is an advanced pattern, but it handles edge cases that the built-in combine cannot.

Query Dependencies

Power Query supports query dependencies: one query can reference another. Suppose you have a query that imports raw sales data and a second query that imports a product lookup table. A third query can merge them (equivalent to a SQL JOIN).

To merge queries: open the primary query, go to Home > Merge Queries, select the secondary query, choose the join key columns, and select the join kind (Left Outer, Inner, Full Outer, etc.). The result is a new query that combines both sources.

Query dependencies create a pipeline: raw data flows through cleaning steps, then through merge steps, then into the final output. Each query is a stage in the pipeline. When you refresh, the entire pipeline re-executes from the source.

Loading to the Data Model

For large datasets (hundreds of thousands of rows), loading to the worksheet is slow and memory-intensive. Instead, load to the Data Model: in the Close & Load dialog, choose "Only Create Connection" and check "Add this data to the Data Model."

The Data Model stores the data in a compressed in-memory format. You can build pivot tables from Data Model queries, and the data does not appear as a worksheet table. This is the foundation for Power Pivot and DAX measures, which are covered in advanced topics.

Error Handling in Pipelines

A pipeline is only as robust as its weakest step. If the source file is missing, the query fails. If the source file has an unexpected schema, the query produces errors. Build error handling into the pipeline:

• Use try ... otherwise in custom columns to catch row-level errors.
• Use Table.TransformColumnTypes with explicit type declarations to catch schema mismatches.
• Document the expected schema so you can diagnose failures quickly.

A pipeline that fails loudly is better than one that fails silently. Silent failures produce plausible-looking but wrong numbers — the most dangerous kind of data quality issue.