To add a column in Power Query for Excel, open the query in Power Query Editor, select the Add Column tab, and choose a tool such as Custom Column, Conditional Column, or Index Column. The new column becomes a recorded step in the query, so it is recreated when the data refreshes rather than being typed into the worksheet once.
What adding a column means in Power Query
Power Query is Excel’s data-import and transformation tool. It reads data from a source, applies a sequence of steps, and loads the resulting table into Excel. Adding a column means defining another value for every row in that transformed table. The value might be calculated from existing fields, assigned according to conditions, or generated as a row number.
This is different from inserting a worksheet column and entering a formula in Excel. A Power Query column is part of the query’s transformation logic. When the source changes and you refresh, Power Query runs the steps again against the newly retrieved rows. That makes it useful for repeatable cleanup, calculations, labels, and reporting fields.
Most added columns are computed independently for each row. For instance, if a table contains Quantity and Unit Price, a custom column can calculate an amount for each record. Other tools have different jobs: an index assigns a sequence, while a conditional column returns labels such as High or Low based on rules.
Open the query and choose a column tool
- In Excel for Microsoft 365 on Windows, select a cell in the query output table, then choose Data > Queries & Connections if the pane is not already visible.
- In the pane, right-click the query and choose Edit to open Power Query Editor. You can also open a query from the Data tab’s query commands, depending on the workbook layout.
- Select the relevant query and inspect the column headings and data types before adding anything.
- On the ribbon, open Add Column and choose the tool that matches the result you need.
- Review the preview, rename the new column if needed, and choose Home > Close & Load to return the result to Excel.
The available commands can vary with the selection and the kind of query. In Excel for Mac, Power Query is available in Microsoft 365, but supported sources and some editor capabilities can differ from Windows. If a command or connector described here is not present, check the current Mac version’s Power Query support or use Excel for Windows for that operation.
Make a calculated column with Custom Column
Use Add Column > Custom Column when the new value requires an expression using existing fields. In the dialog, enter a name for the column and build the expression using the names listed at the right. In the formula box, column references use square brackets. For example, with numeric columns named Quantity and Unit Price, the expression =[Quantity] * [Unit Price] produces a line amount for each row.
Other practical expressions include combining text fields, extracting a year from a date, or applying a fixed rate. For example, with a date field named OrderDate, =Date.Year([OrderDate]) returns its year. A custom column can also use an if-then-else expression, such as if [Quantity] > 10 then "Bulk" else "Standard", to label each record. Power Query uses the M language, so its functions and syntax are not the same as worksheet formula syntax.
The dialog checks the expression and displays a preview. Choose OK to add it as a step. Power Query typically assigns a data type automatically, but automatic detection is not a substitute for checking it. For example, a numeric calculation may need to be set to a decimal or whole-number type, while a year may be best represented as a whole number.
For a more advanced view, the M code for an added field is often represented by a step like Table.AddColumn(PreviousStep, "Line Amount", each [Quantity] * [Unit Price]). This illustrates the operation: use the previous table, name the added column, and calculate a value for each row. You do not need to edit M directly for common tasks; the ribbon creates these steps for you.
Use Conditional Column for readable rules
Add Column > Conditional Column is designed for rules that return different results depending on values in one or more fields. In the dialog, select a column, an operator, and a comparison value, then specify what to output when the condition is true. Add further clauses when needed and provide an Else result for rows that do not meet a condition.
For example, a sales table might need a shipping label based on order value. A rule could return Free when the value is at least 50 and Paid otherwise. A tier classification could test for a high threshold first, then a middle threshold, and assign the lowest tier in the Else result. The order matters: Power Query evaluates the rules in sequence, so put the most specific or highest-priority condition where it will be applied correctly.
Conditional Column is often easier to maintain than a long nested expression, especially when business users may need to review the rules later. Use Custom Column instead when the logic involves calculations or functions that the conditional dialog cannot express conveniently. Either approach creates a query step and can be adjusted later from the Applied Steps list.
Generate examples, indexes, or copies
Power Query offers several other ways to add values without writing a typical calculation:
- Column From Examples: Provide one or more desired outputs, and Power Query attempts to infer a transformation from the existing fields. It is useful for patterns such as extracting a code from text or combining parts of a name. Check the preview across varied rows; examples that fit a few records may not describe every case.
- Index Column: Choose Add Column > Index Column, then select an index beginning at zero, beginning at one, or a custom starting value. An index is a row position in the query at that point in the steps, not a permanent identifier for a source record.
- Duplicate Column: Use Add Column > Duplicate Column to create a copy before changing or extracting values. This is helpful when retaining the original text while making a cleaned or split version.
Index values can change if rows are added, removed, or reordered before the index step. If you need a stable business identifier, use an actual source key such as an order ID rather than treating an index as a unique permanent ID. If an index is intended to reflect a particular sort order, sort the table before adding the index and verify that the sort itself is based on reliable fields.
Set the right data type and handle blanks
A new column’s data type affects later operations. Dates should be dates, identifiers that may contain leading zeros should generally remain text, and amounts should use an appropriate numeric type. A value such as 00127 can lose its leading zeros if treated as a number, even though it may be a code rather than a quantity. Use the type icon beside the column heading or the ribbon’s data type control to set the intended type.
Pay attention to nulls, which represent missing values in Power Query. An expression that works for ordinary rows may return an error or an unexpected result when one of its inputs is null, depending on the operation. Decide what missing data should mean before replacing it: a blank price may need to remain blank rather than silently become zero. You can use conditional logic in a custom column to return null or a chosen label when an input is missing.
Also distinguish null from an empty text value. A null is missing data; an empty string is text containing no characters. They may look similar in a preview but can behave differently in comparisons and cleanup steps. If the source has inconsistent blanks, inspect and standardize them deliberately rather than assuming every empty-looking cell has the same underlying value.
Add Column versus Transform
The Add Column tab creates a separate field while retaining the original field. The Transform tab changes an existing column in place. For instance, if you need both a raw product description and a standardized version, add a new column or duplicate the original before transforming it. If the old value is no longer needed and you want to replace it, a transform operation may be simpler.
This distinction is useful when cleaning source data. Keeping both original and cleaned fields makes it easier to compare results and diagnose an unexpected transformation, but it also increases the width of the output table. Once the query is verified, remove columns that are not needed in the final result to keep the workbook manageable.
Do not confuse adding a column with merging queries. A merge matches rows from two tables using one or more key columns; expanding the merged table can then bring fields from the other query into the result. If the value you need exists in a separate lookup table, a merge may be the appropriate method rather than a custom calculation.
Refresh behavior and query maintenance
Each operation appears in the Applied Steps pane. Selecting a step previews the table as it looked at that point, and the gear icon beside some steps lets you reopen their settings. Steps run in order: a custom column can only use fields that exist at the moment that step runs. If an earlier step removes or renames a referenced field, a later custom column may fail.
When you refresh the query, Power Query re-applies its steps to the source data. This makes a well-designed added column more dependable than manually filling a worksheet formula after every import. However, refresh does not repair a changed source schema automatically. If a source renames a required heading, changes a date into incompatible text, or removes a field used by your expression, the query may return an error and need adjustment.
For repeatable work, use descriptive names for added columns and keep the calculation close to the steps that prepare its inputs. Avoid unexplained names such as Custom1 when a name like Order Year or Line Amount makes the result clear. A good name and sensible data type help other workbook users understand what the column represents.
Common errors and how to fix them
- The formula says a field was not found: Check the spelling and punctuation of the heading, including spaces. Confirm the column has not been renamed or removed in an earlier step. Selecting the field from the dialog’s column list can prevent typing mistakes.
- The result shows Error for some rows: Inspect those records for nulls, text in a numeric field, invalid dates, or values that do not fit the operation. Use the error details in the preview to identify the failed value, then correct the source or add deliberate handling for that case.
- The output has the wrong type: Set the column type explicitly after creating it. A number stored as text may sort alphabetically, and a date represented as text may not support date operations as expected.
- The index changes after refresh: This is expected if the rows or their order change before the index step. Use a source key for stable identity, or establish a deterministic sort before generating a position-based index.
- The new column is missing in the worksheet: Check that you loaded the intended query and that the final load step includes the column. In the editor, confirm the column is present at the last step and that no later step removes it.
When diagnosing an issue, work from the earliest step where the preview stops matching expectations. Fixing the first incorrect step is usually clearer than adding another workaround at the end of the query.
Frequently Asked Questions
How do I add a formula column in Power Query?
Open the query, choose Add Column > Custom Column, enter a column name and an M expression using references such as [Quantity], then select OK. Check the preview and assign the correct data type before loading.
Can I add a column without changing the original data?
Yes. Add Column creates a separate field and leaves the existing fields in the query. A Transform operation changes the selected field instead, so choose based on whether you need to preserve the original value.
How do I add a row number in Power Query?
Choose Add Column > Index Column and select From 0, From 1, or a custom starting point. Sort first if the index must follow a particular order, and remember it is a position rather than a durable record ID.
Does a Power Query custom column update when I refresh?
Yes. The expression is stored as a query step and is applied again during refresh. It will work as long as the source still supplies the fields and compatible values that the step expects.
Can I use Excel worksheet formulas in a custom column?
Not directly. Custom Column uses Power Query’s M language, with its own functions and syntax. You can create an equivalent calculation in M, or load the data first and use a worksheet formula if that better suits the task.
Why does my conditional column return the wrong label?
Check the order of the conditions, comparison operators, and Else value. Rules are evaluated in sequence, so a broad condition placed before a more specific one can capture rows you intended to classify differently.
Related
- Excel Insert X Rows: Fast Methods for Any Worksheet
- Excel Insert a Row Every X Rows: Methods That Work
- Excel Insert Lines Between Each Row: Simple Methods
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Hide a Column If a Cell Is Blank in ExcelYes—but Excel has no built-in setting that automatically hides a column when a cell becomes blank. In Excel…
- SUMPRODUCT IF Function: How to Sum with ConditionsNo, Excel does not have a separate SUMPRODUCTIF or “SUMPRODUCT IF” function. To sum values that meet conditions,…
- Excel Formula for Age at a Certain Date Using DATEDIFTo calculate someone’s age on a particular date in Excel, use =DATEDIF(A2,B2,"Y"), where A2 contains the birth date…
- VLOOKUP and HLOOKUP in Excel: Differences and ExamplesVLOOKUP searches down the first column of a table and returns a value from a column to its…
- Excel Formula Bar Disappeared? How to Show It AgainIf the Excel formula bar disappeared, turn it back on from View > Show > Formula Bar in…
- Excel ROUND: Does It Round Positive or Negative Numbers?Excel’s ROUND function rounds both positive and negative numbers to the nearest value at the specified decimal place.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.