Webbo3 Data Analysis Bootcamp · Excel Module · Lesson 8

Power Query Editor: Transforming Raw Data into Analysis-Ready Datasets

A comprehensive lesson on the Power Query Editor interface, removing and renaming columns, changing data types, splitting and merging fields, filtering, adding custom columns, and tracking every transformation in the Applied Steps panel.

So far in this bootcamp you have learned to clean data manually inside Excel sheets, using Flash Fill, Find and Replace, and formulas. Those methods work, but they are destructive. Once you delete a column or overwrite a value, the original data is gone unless you kept a separate copy. Power Query changes the entire paradigm. It is a dedicated ETL engine inside Excel, and increasingly inside Excel for the web, that lets you connect to data sources, transform them, and load the cleaned result into your workbook, all while preserving the original data untouched. Every transformation is recorded as a step, reversible, and automatically reapplied whenever the source data refreshes. This lesson teaches you to use the Power Query Editor as a professional data analyst would: methodically, non-destructively, and with full traceability.

1. Power Query Editor Interface

Power Query is available in Excel 2016 and later as a native feature under the Data tab, labeled Get and Transform Data. In Excel for Microsoft 365, you can now create and edit queries directly in the browser, not just on desktop, making it accessible from any device with a subscription. To open the Power Query Editor, select any data range in your worksheet, go to the Data tab, and click From Table/Range. If you are importing from an external source like a CSV, text file, or database, click Get Data, choose your source, and the Editor opens automatically after the initial connection.

The Queries pane. On the left side of the Editor, the Queries pane lists every query in your workbook. A query is a single transformation pipeline: one source, one sequence of steps, one output table. You can have multiple queries in one workbook, and you switch between them by clicking their names. Double-click a query name to rename it. Use descriptive business names like Sales_2026_Q1 or Customer_Master, not Query1 or Query2. If your workbook grows complex, right-click in the Queries pane and create Groups to organize queries into folders like Raw_Data, Cleaned, and Final.

The Data Preview. The center of the Editor shows a live preview of your data after the currently selected step. This is not the final loaded data. It is a preview, usually the first one thousand rows, that lets you see the effect of each transformation before you commit. The column headers display small icons indicating the current data type: 123 for whole numbers, 1.2 for decimals, ABC for text, and a calendar for dates. Click these icons to change types directly from the header.

The Ribbon. At the top, the ribbon contains four main tabs. The Home tab holds the most common actions: Close and Load, Refresh Preview, Remove Rows, Remove Columns, and Merge Queries. The Transform tab contains structural operations: changing data types, splitting columns, pivoting and unpivoting, and grouping. The Add Column tab is where you create new columns from existing ones, including custom columns, conditional columns, and index columns. The View tab controls the interface itself: you can show or hide the Formula Bar, the Queries pane, and enable data profiling tools like Column Quality and Column Distribution.

The Formula Bar. Below the ribbon, the Formula Bar displays the M language code for the currently selected step. M is the functional programming language behind every Power Query transformation. Even if you never write M manually, watching the Formula Bar teaches you how the Editor translates your clicks into code. If the Formula Bar is hidden, enable it from the View tab. Professionals keep it visible at all times because it is the fastest way to understand exactly what a step does and to make precise edits without navigating multiple dialog boxes.

The Query Settings pane. On the right side, the Query Settings pane shows two sections: Properties, where you rename the query and add a description, and Applied Steps, which is the backbone of Power Query. Every click you make in the Editor generates a step here, in sequence, from top to bottom. The data flows through each step in order, and the preview updates accordingly. This pane is so important that it gets its own section later in this lesson.

2. Removing Columns and Rows

The first rule of efficient data transformation is to remove what you do not need before you process what you do. Importing every column from a source and cleaning them all is wasteful. It bloats your workbook, slows refresh times, and creates unnecessary complexity. Power Query makes removal precise and reversible.

Removing columns. In the Data Preview, select the column or columns you want to remove. You can select multiple columns by holding Ctrl while clicking headers. Then go to the Home tab and click Remove Columns, or right-click the selected header and choose Remove Columns. Alternatively, if you know exactly which columns you want to keep and there are many you want to discard, select the columns you want to keep, then click Remove Columns → Remove Other Columns. This is often faster. Every removal creates a step in the Applied Steps pane. If you later realize you need a removed column, simply delete the removal step from Applied Steps, and the column reappears instantly.

Removing rows. Under the Home tab, the Remove Rows dropdown offers several options. Remove Top Rows and Remove Bottom Rows are useful when your source file contains header metadata or footer totals that are not part of the actual dataset. Remove Alternate Rows deletes every nth row, which is helpful for structured reports with blank separator rows. Remove Duplicates compares all columns or selected columns and keeps only the first occurrence of each unique combination. Remove Blank Rows deletes rows where every column is empty. Remove Errors deletes rows where any column contains an error, which is useful after a type conversion that failed on malformed data.

A critical best practice: remove columns and filter rows as early in your query as possible. Power Query processes steps sequentially. If you remove twenty unnecessary columns in step two, every subsequent step operates on a smaller dataset, which reduces memory usage and speeds up refresh. This also improves query folding, the process where Power Query pushes transformations back to the source database as native SQL, which is only possible for operations applied early and simply.

3. Renaming Columns

Column names from external sources are often unfriendly. They may contain spaces, special characters, mixed cases, or cryptic abbreviations like Cust_Nm or Dt_1. Clean, consistent column names make every subsequent step easier, from writing formulas to building Pivot Tables and dashboards.

Renaming a single column. Double-click the column header in the Data Preview. The header becomes editable. Type the new name and press Enter. Alternatively, right-click the header and choose Rename. This creates a Renamed Columns step in the Applied Steps pane.

Renaming multiple columns efficiently. If you are importing a dataset with dozens of columns, renaming them one by one is tedious. Select the columns you want to rename, then go to the Transform tab and choose Rename Columns. Or, use the Advanced Editor to write a single M formula that renames multiple columns at once. For example, Table.RenameColumns can map old names to new names in one step. However, for beginners, the manual approach is safer because you see each change immediately.

Naming conventions for analysis. Use short, stable, analysis-oriented names rather than presentation labels. Avoid spaces if you plan to reference columns in custom formulas, though Power Query handles spaces gracefully by wrapping names in square brackets. Use consistent casing: customer_id, order_date, and total_amount are easier to read and less error-prone than CustomerID, orderDate, and TotalAmount. If you are building a data model that will feed Power BI or a dashboard, these names become the field names in your visuals, so clarity matters beyond the query itself.

A practical habit: rename columns immediately after importing and removing unnecessary ones, before you change data types or add custom calculations. This establishes a clean foundation and prevents you from writing formulas that reference cryptic original names.

4. Changing Data Types

Data types determine how Power Query interprets and processes your values. A column imported as text cannot be summed. A date stored as text cannot be sorted chronologically. Power Query attempts automatic type detection when it first connects to a source, but this detection is not always correct, especially with ambiguous formats like 01/02/2026, which could mean January 2 or February 1 depending on your locale.

Changing types from the header. Click the data type icon on the left side of any column header. A dropdown appears with the available types: Text, Whole Number, Decimal Number, Currency, Date, Time, Date/Time, Percentage, True/False, and Binary. Select the appropriate type. The entire column converts immediately, and a Changed Type step is added. If any value cannot be converted, Power Query displays Error in that cell. You can filter to see only errors, diagnose the problematic values, fix them, and then retry the type change.

Changing types using the Transform tab. Select one or more columns, then go to Transform → Data Type → choose your type. This is useful when you want to apply the same type to multiple columns at once, for example converting three currency columns from Text to Decimal Number simultaneously. You can also use Detect Data Type from the Transform tab, which forces Power Query to re-evaluate all columns and guess their types based on the first two hundred rows. Use this cautiously on large datasets, because a value that looks like a number in the first two hundred rows might actually be text further down, and automatic detection will misclassify it.

Using locale for dates and numbers. If your data source uses a different regional format than your Excel installation, standard type changes may fail. For example, a CSV from a US system might store dates as MM/DD/YYYY while your system expects DD/MM/YYYY. In this case, go to Transform → Data Type → Using Locale. Select the type you want, for example Date, and then choose the source locale, for example English (United States). Power Query converts the values correctly according to the source locale, not your local system settings.

A critical warning: never skip the data type validation step. Incorrect types are the root cause of the majority of errors in downstream analysis. A column of invoice numbers stored as numbers will lose leading zeros. A percentage stored as text will not calculate correctly in summaries. Always verify every column's type before closing the Editor.

5. Splitting and Merging Columns

Real-world data often combines multiple pieces of information into a single column, or spreads one logical field across several. Power Query provides precise tools to restructure this without writing complex string formulas in Excel cells.

Splitting columns. Select the column you want to split, then go to the Home or Transform tab and click Split Column. You have several options. By Delimiter splits the text at a specific character, such as a comma, space, or hyphen. For example, splitting a Full Name column by space creates First Name and Last Name columns. By Number of Characters splits at a fixed position, which is useful for product codes where the first four characters represent the category and the rest represent the item number. By Positions lets you specify multiple split points at once. By Lowercase to Uppercase and similar options detect case transitions, which is useful for parsing camelCase or PascalCase identifiers.

When you split by delimiter, a dialog asks which delimiter to use and whether to split at the leftmost occurrence, the rightmost occurrence, or every occurrence. If you choose every occurrence on an address like 123 Main St, Suite 400, you get three columns: 123 Main St, Suite, and 400. If you only want the street and the suite separated at the first comma, choose at the leftmost delimiter. You can also specify a custom delimiter if your data uses an unusual separator like a pipe symbol.

Merging columns. Select two or more columns, then go to Transform → Merge Columns, or Add Column → Merge Columns depending on whether you want to replace the original columns or create a new one while keeping the originals. A dialog appears asking for a separator and a new column name. The separator can be a space, comma, custom text, or nothing at all. For example, merging First Name and Last Name with a space separator produces Full Name. Merging City and Country with a comma and space produces Location. This is cleaner than using Excel's CONCATENATE or TEXTJOIN functions because it happens before the data ever reaches your worksheet, keeping your workbook formula-free and lightweight.

A practical tip: when splitting columns, always check the Advanced Options in the split dialog. You can choose to split into separate rows instead of separate columns, which is powerful for unpacking comma-separated values in a single cell into multiple rows, one per value. This is called unpivoting by delimiter and is a common requirement when dealing with survey responses or tag lists.

6. Replacing Values and Filling Down

Data exported from operational systems often contains inconsistent text, blank cells that should inherit the value above them, or placeholder codes that need translation into human-readable labels. Power Query handles these transformations natively.

Replacing values. Select a column, then go to Transform → Replace Values, or right-click the header and choose Replace Values. In the dialog, type the value to find and the value to replace it with. You can match the entire cell contents or any part of the cell. You can also choose to match using a regular expression for advanced pattern matching, though this requires enabling the option in the dialog. For example, replacing Lagos State with Lagos, or replacing all instances of N/A with a blank. If you need to replace values across multiple columns simultaneously, select all relevant columns first, then apply Replace Values. The operation creates a single step that affects all selected columns.

Replacing errors. After a type conversion, you may have error cells that you want to handle uniformly. Select the column, go to Transform → Replace Errors, and choose whether to replace errors with a default value, a null, or a custom value. This is safer than removing error rows if the errors represent a minority of the data and you want to preserve the rows for analysis.

Filling down and filling up. In many reports, especially those exported from accounting or ERP systems, category names appear only once at the top of a group, with the rows below left blank until the next category. To analyze this data properly, each row needs its category label. Select the column with the gaps, then go to Transform → Fill → Down. Power Query copies the value from each populated cell into all blank cells below it until it hits the next populated cell. Fill Up does the opposite: it copies values upward into blank cells above. These two commands solve a problem that is tedious and fragile in Excel formulas, and they do it in a single reversible step.

A practical workflow: after importing a financial report, you often need to fill down the month name, then fill down the cost center, then replace the placeholder 0.00 values with actual nulls so they do not skew your averages. These three steps, Fill Down, Fill Down, Replace Values, take thirty seconds in Power Query and would take thirty minutes of careful formula work in standard Excel.

7. Filtering Rows

Filtering in Power Query is conceptually similar to filtering in Excel, but it happens before the data loads into your worksheet, which means you can filter millions of rows down to a relevant subset without ever hitting the Excel row limit or slowing down your workbook.

Basic filtering. Click the filter dropdown arrow on any column header. You will see a list of distinct values in that column, along with a search box. Uncheck values you want to exclude, or use the Text Filters and Number Filters submenus for condition-based filtering. Text Filters includes Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. Number Filters includes Equals, Does Not Equal, Greater Than, Less Than, and Between. Date Filters includes Before, After, Between, and dynamic options like In the Previous Month or In the Next Quarter.

Filtering by data quality. Enable Data Profiling from the View tab to see Column Quality indicators next to each header. These show the percentage of valid, error, and empty values in each column. You can click the quality indicator and filter directly to show only errors or only empty values, which is the fastest way to find and fix data quality issues.

Advanced filtering with multiple columns. You can apply filters on multiple columns simultaneously. Each filter creates an additional criterion in the same Filtered Rows step. For example, filtering Region to Lagos and Order_Date to After 2026-01-01 creates one step that applies both conditions. To see the exact logic, click the gear icon next to the Filtered Rows step in the Applied Steps pane. This opens the dialog showing all active filters, which you can edit, reorder, or remove individually.

A best practice for large datasets: filter early. If you only need data from 2026, apply a date filter as your first or second step. This reduces the number of rows Power Query processes in every subsequent transformation, which speeds up refresh and reduces memory consumption. If your source is a SQL database and the filter is simple enough, Power Query may even fold the filter into the native SQL query, meaning the database server does the filtering before any data reaches Excel.

8. Adding Custom Columns

When the built-in transformations are not enough, you create custom columns using the M formula language. This is where Power Query becomes as powerful as writing Excel formulas, but with the advantage of being applied to the entire dataset before loading.

Creating a custom column. Go to the Add Column tab and click Custom Column. A dialog appears with a New Column Name field, a Data Type dropdown, and a Custom Column Formula box. In the formula box, you write M language expressions that reference existing columns by their names in square brackets. For example, if you have a Quantity column and a Unit_Price column, you can create a Revenue column with this formula:

[Quantity] * [Unit_Price]

Click OK, and the new column appears in the preview. The Available Columns list on the right side of the dialog lets you insert column names into your formula without typing them manually, which reduces typos. If your column name contains spaces or special characters, Power Query automatically wraps it in double quotes inside the brackets, like [#"Unit Price"].

Conditional logic in custom columns. You can use if-then-else logic, similar to Excel's IF function but with M syntax:

if [Total_Sales] > 50000 then "High" else if [Total_Sales] > 10000 then "Medium" else "Low"

M is case-sensitive. The keywords if, then, and else must be lowercase. Column names must match exactly, including spaces. A yellow warning icon appears in the dialog if your syntax is invalid, with an explanation of the error.

Text and date functions. M provides a rich library of functions. Text.Combine merges text from multiple columns with a separator. Text.Start and Text.End extract substrings. Date.Year, Date.Month, and Date.Day extract components from a date column. Number.Round rounds decimal values to a specified precision. These functions are often more reliable than their Excel counterparts because they handle null values and type mismatches gracefully.

A practical example: you have an Order_Date column and you need a Fiscal_Quarter column where your fiscal year starts in April, not January. There is no built-in button for this. You create a custom column with a formula that checks the month number and returns Q1, Q2, Q3, or Q4 based on your fiscal calendar. This calculation is applied to every row in the dataset, and the result is loaded into Excel as a native column, ready for Pivot Tables and charts.

9. Applied Steps Panel: Tracking All Transformations

The Applied Steps pane is the single most important feature of Power Query. It is the audit trail, the undo history, and the automation script all in one. Every transformation you perform, from removing a column to adding a custom formula, is recorded here as a named step in sequential order. When you refresh your data source tomorrow, next week, or next year, Power Query replays these steps automatically on the new data, producing the same cleaned output without any manual repetition.

Reading and navigating steps. Each step has a name and a gear icon. The name describes the transformation, for example Removed Columns, Changed Type1, or Added Custom. The gear icon opens the dialog for that step, allowing you to edit its parameters without deleting and recreating it. Clicking any step in the pane shows the data preview as it existed at that exact point in the pipeline. This is invaluable for debugging. If your final output looks wrong, you can click backward through the steps to find exactly where the data diverged from your expectation.

Renaming steps. The default names are functional but generic. Right-click any step and choose Rename to give it a descriptive name. Instead of Changed Type1, rename it to Set Currency Types. Instead of Filtered Rows, rename it to Filter to 2026 Sales. This documentation is not just for you. When a colleague opens your query six months later, or when you open it yourself after working on other projects, the renamed steps tell a clear story of what happened and why. This is professional data governance.

Reordering and deleting steps. You can drag steps up or down to change their order, but be cautious. Moving a step that references a column before the step that creates that column will break the query. Power Query will show an error in the preview and highlight the problematic step in red. You can delete any step by clicking the X next to it. Deleting a step removes its effect but leaves all other steps intact. If you delete a Removed Columns step, the columns reappear. If you delete a Changed Type step, the columns revert to their previous types. This non-destructive workflow is impossible in standard Excel without keeping multiple backup copies of your file.

Inserting steps mid-pipeline. If you realize you need to add a transformation between existing steps, click the step that should come immediately before the new one, then perform the transformation. Power Query inserts the new step at that position and automatically adjusts all subsequent steps. This is useful when you discover late in the process that a column needs to be cleaned before it can be used in a merge or a custom formula.

Viewing the M code. For the complete picture of your query, go to the View tab and click Advanced Editor. This opens a window showing the entire M script for the selected query, from the source connection through every step to the final output. You can edit the code directly here, which is useful for bulk operations like renaming multiple columns in one function call or creating reusable custom functions. The Editor validates syntax and highlights errors before you confirm. Even if you rarely write M from scratch, reading the Advanced Editor regularly teaches you the language and expands what you can accomplish beyond the ribbon buttons.

Using AI to Move Faster in Power Query

Power Query is already a visual tool, but complex transformations, custom M formulas, and debugging broken steps can still consume significant time. AI can act as a tutor, a code generator, and a debugger, accelerating your workflow while you retain full control over the logic.

1. Generate M language formulas for custom columns with natural language.
If you need a custom column but do not know the exact M syntax, describe what you want in plain English. For example: "Write a Power Query M formula that calculates a 10 percent discount on the Unit_Price column, but returns the original price if the Quantity is less than 10." AI will produce an M expression like if [Quantity] >= 10 then [Unit_Price] * 0.9 else [Unit_Price]. Copy it into the Custom Column dialog, verify the column references match your actual headers, and click OK. This is especially useful for date calculations, text parsing, and nested conditional logic where M syntax differs from Excel formulas.

2. Debug broken steps by pasting the error into an AI assistant.
When a step turns red and shows an error like Expression.Error: The column 'Customer_ID' of the table wasn't found, paste the error message and your last five step names into an AI assistant and ask: "This Power Query step is failing after I renamed a column. How do I fix the reference?" AI will explain that subsequent steps still reference the old column name and need to be updated, or it will suggest using the Advanced Editor to find and replace the old name across the entire M script. This turns a cryptic error into a five-minute fix instead of a thirty-minute manual hunt.

3. Use AI to design efficient query structures.
If you are unsure whether to split a complex transformation into multiple queries or keep it in one long pipeline, describe your data sources and goals to AI: "I have three CSV files for sales, products, and customers. Should I merge them in one query or use separate queries with references?" AI will likely recommend separate staging queries for each CSV, a cleaning query that references the staging queries, and a final query that merges the cleaned data. This modular approach, using query references rather than loading everything into one monolithic script, is the professional standard and makes debugging far easier.

4. Generate data cleaning checklists from your source description.
Before you open the Power Query Editor, describe your raw data to AI: "I have a sales export with mixed date formats, currency symbols in the revenue column, blank rows between sections, and product codes that need to be split into category and item number. What Power Query steps should I apply, and in what order?" AI will generate a step-by-step cleaning sequence: remove top rows for headers, fill down category names, split product codes, remove currency symbols and change type to number, parse dates using locale, and filter out blank rows. Use this as your roadmap in the Editor. The order matters, and AI can suggest an optimal sequence that minimizes errors and maximizes query folding.

5. Verify AI-generated M code before applying it.
M language is case-sensitive, column names must match exactly, and some functions behave differently depending on the data type. Always paste AI-generated formulas into the Custom Column dialog and check for the yellow warning icon before clicking OK. If the formula references a column that does not exist or uses a function incompatible with your version of Excel, the dialog will flag it immediately. Test the result on the preview data, scroll through a few rows to spot obvious errors, and only then close the Editor and load the data. AI accelerates your work, but the final responsibility for correct data rests with you.

A habit worth building from this lesson onward: before starting any Power Query transformation, write down the five steps you think you will need. Then ask AI to review and optimize that sequence. Compare its suggestions to your plan, implement the improvements that make sense, and document the final sequence by renaming your Applied Steps clearly. This habit, plan, consult, optimize, document, transforms Power Query from a tool you use into a workflow you master.

Next lesson: loading data into Excel, managing query refresh settings, and combining multiple data sources.