To add a column in Excel Power Query, open the Power Query Editor, select the Add Column tab, and choose Custom Column, Conditional Column, or another suitable command. For a calculated field, enter an expression such as =[Quantity] * [Unit Price], choose a name, and select OK; the new column is saved as a query step and is recalculated when the query refreshes.
What an added Power Query column does
Power Query is Excel’s data-import and transformation tool. It lets you shape data before loading the result into a worksheet or the Data Model. An added column is part of that transformation: Power Query applies a recorded step to the query’s incoming rows and returns the result as another field. It does not, by itself, add a formula to the original source file or change the source table.
This distinction matters when the source is refreshed. If a query reads a sales file and you add a profit calculation, Power Query can perform that calculation again for the newly imported rows on refresh. A formula typed into a worksheet next to the loaded results is a separate worksheet formula, and it may need different handling as the output grows or changes.
Power Query records operations in the Applied Steps list. Steps run in sequence, so a column can refer to fields created earlier in the query, but it cannot use a field that has not yet been created or that was removed in an earlier step. You can select a step to inspect its result, or delete or edit a step if the transformation needs changing.
Open the Power Query Editor
In Excel for Microsoft 365 on Windows, a common way to begin with data already in the workbook is to select a cell in the table and choose Data > From Table/Range. If Excel asks whether the range has headers, confirm the setting that matches the data. For a file or another source, use the relevant command in Data > Get Data, choose the source, and select Transform Data when prompted. To edit a query already in the workbook, open Data > Queries & Connections, then double-click the query.
In the Editor, check that the preview shows the intended headers and rows before adding a field. When your transformations are ready, use Home > Close & Load to return the results to Excel. The exact load choices depend on the query and workbook setup; for example, a query can be loaded to a worksheet or, where available, to the Data Model.
Power Query is also available in Excel for Mac, but source support and the interface can differ by Excel version and update. The Windows menu paths above may not match a Mac build exactly. Excel for the web has more limited query-authoring capabilities than the Windows desktop application, so use the desktop Editor if the required add-column command is not available.
Choose the right kind of column
The Add Column tab offers different tools for different jobs. Picking the closest fit makes a query easier for someone else to understand and maintain.
- Custom Column: Create a calculation or text transformation using an expression. Use it for fields such as line total, a combined label, or a rule that is not covered by a simpler command.
- Conditional Column: Build an if/then-style result through dialog boxes. It is useful for categories such as “Late” when a date is past due and “On time” otherwise.
- Index Column: Add sequential numbers, usually starting at zero or one. This can identify row positions within the current query order, but it is not a permanent business identifier.
- Duplicate Column: Copy an existing field into a new column before changing the copy. This preserves the original field for comparison or later use.
- Column From Examples: Show Power Query examples of the output you want, and let it infer a transformation. Review the result carefully, especially when examples might fit more than one rule.
Commands that derive a column from text, dates, times, or structured data can also be useful when the task is specific. For instance, extracting a month from a date is often clearer than writing a custom expression, while a more involved calculation may be easier to express in a custom column.
Add a calculated column with Custom Column
For a formula-like field, open the query and choose Add Column > Custom Column. In the dialog, type a descriptive new-column name, enter the expression, and check the available-column list for the exact field names. Select OK, then inspect the values and the column’s data type. The expression is evaluated for each row; references such as [Quantity] refer to fields in that row.
For example, suppose a sales table has numeric columns named Quantity and Unit Price. With those fields present, a custom expression of =[Quantity] * [Unit Price] creates a line total. If a row has a quantity of 3 and a unit price of 12.50, the result is 37.50. This is a Power Query expression entered in the Custom Column dialog, not a worksheet formula to paste into a cell.
You can also combine text. If the table contains a customer number and a region, a custom expression can join them with a separator. Power Query uses M language for these expressions; text conversion may be needed when combining a number with text. An example expression is =Text.From([Customer ID]) & "-" & [Region]. The ampersand joins text values, and Text.From converts the customer ID to text first.
Use the dialog’s error information and the preview to catch misspelled field names or incompatible values. A multiplication expression expects values that can be treated as numbers. If imported values contain text such as “unknown,” first decide how those rows should be handled—perhaps replace the value, convert the column with an appropriate error policy, or make the new calculation conditional. Silently treating every unusual value as zero can hide a data-quality problem.
Make a rule-based column
Choose Add Column > Conditional Column when the result depends on one or more tests. Give the new field a name, select the source column and comparison, enter the comparison value, and specify the output for a match. Add additional clauses for further cases, then set the result for rows that meet none of the listed conditions. The dialog builds the logic without requiring you to type the entire M expression.
For example, an order table could classify a row as “Late” when its status is not delivered and its promised date is before the relevant date, with other rows assigned another category. The precise conditions depend on the business definition: an order due today may or may not count as late, and a blank delivery date may need its own treatment. Make those rules explicit rather than assuming the query can infer what a category means.
Condition order matters when rules overlap. If a value could satisfy more than one condition, arrange the clauses so the intended case is tested first. After creating the column, review examples at the boundaries: a date exactly on the cutoff, a blank value, and a value just above or below a threshold. This is a practical way to catch a rule that appears reasonable but classifies edge cases incorrectly.
Set data types and check the result
Power Query assigns data types such as text, whole number, decimal number, date, and date/time. A new column can have the wrong type even when its preview looks plausible. A number stored as text may not sort or calculate as expected, and a date interpreted incorrectly can change comparisons. Select the new column and use the data-type control in the Editor to choose the appropriate type. For a monetary calculation, a decimal or fixed-decimal type is generally more suitable than text.
Look for error cells, unexpected nulls, and values that do not match the intended rule. Check more than the first few rows if the data contains different categories or unusual cases. If the calculation depends on two columns, confirm that both source columns have the expected types before diagnosing the new one. Correcting the source types before the calculation often makes the expression simpler and more reliable.
Also check whether the new field has the name you want and appears in a useful position. Adding a column does not always put it where it belongs in the final report. You can reorder columns in the Editor; remember that this is another recorded query step. Clear names such as “Line Total” are more useful to workbook readers than generic names such as “Custom.”
Refresh behavior and common problems
After loading the query, use Data > Refresh All to refresh workbook queries, or refresh an individual query from the workbook’s query controls. Power Query reruns its steps against the available source data. A calculated column will therefore be recalculated for rows returned by the refreshed query. If a refresh adds rows but not the expected results, confirm that the query’s source includes those rows and that no later step filters them out.
- “The column wasn’t found” or a similar error: The source header may have changed, or a prior step renamed or removed the field. Check the step immediately before the failing step and update the reference.
- Errors after a type change: Some source values may not convert to the selected type. Inspect the error details and decide how those records should be treated rather than applying a blanket fix without checking.
- The result looks like text: Set the new column’s data type explicitly, and verify that the inputs themselves are not text disguised as numbers or dates.
- New records do not appear: Check the source range, file location, filters, and query steps. A query cannot return records that its source or later transformations exclude.
- Different results after refresh: Review steps involving row order, dates, locale-sensitive parsing, and changing source headers. An index, in particular, reflects the current row order rather than a fixed identity.
Power Query column or worksheet formula?
Use Power Query when the column is part of a repeatable import-and-cleanup process. It is especially helpful when the same transformation must be applied every time updated files arrive, when source data needs reshaping before analysis, or when several reports should use the same cleaned result. The logic lives with the query and can be refreshed along with its source.
Use an Excel worksheet formula when users need to edit or inspect the calculation directly in cells, or when the result must respond immediately to worksheet inputs without refreshing a query. A worksheet formula may also be simpler for a small, one-off calculation beside a table. These approaches can coexist: Power Query can prepare the imported data, and worksheet formulas can perform calculations that depend on user-entered workbook values. Keep in mind that a query refresh can replace or reshape its loaded output, so place hand-entered data outside the query’s output area.
Power Query is not a live cell-by-cell formula engine. Changes in the source are reflected when the query refreshes, subject to the source connection and refresh settings. This refresh-based behavior is a benefit for repeatable data preparation, but it is different from formulas that recalculate as soon as an input cell changes.
Frequently Asked Questions
How do I add a custom column in Excel Power Query?
Open the query in Power Query Editor, choose Add Column > Custom Column, enter a name and expression, and select OK. Review the preview and set the result’s data type before loading or refreshing the query.
Can I add a column in Power Query without changing the source file?
Yes. A column added in the query is a transformation in the workbook’s query; it does not normally write that new field back into the original source file. The transformed output appears when the query is loaded to Excel or another supported destination.
Will a Power Query column update when I add new rows?
It is recalculated when the query refreshes, provided the new rows are included in the query’s source and are not removed by another step. Refreshing cannot include rows that fall outside a fixed source range or are excluded by a filter.
How do I add a row number in Power Query?
In the Editor, choose Add Column > Index Column, then select the starting option you need, such as starting at zero or one. The index follows the query’s row order, so sort the data first if the numbering needs to follow a particular order.
Why does my custom column show errors?
Common causes include misspelled or missing field names, values of incompatible types, and null or unexpected source values. Check the error details, confirm the input column types, and decide how exceptional rows should be handled before changing the expression.
Related
- Project Tracker Spreadsheet Software Explained
- How to Use Slide Master View in PowerPoint Effectively
- Project Tracker Spreadsheet: Free Options and Setup
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Where Is Quick Styles in Word? Find and Use the GalleryIn Microsoft Word for Microsoft 365, Quick Styles are in the Styles gallery on the Home tab. On…
- How to Archive Older Emails in Outlook Without Losing ThemTo archive older emails in Outlook, select the messages and choose Archive to move them out of your…
- Excel Not Responding? How to Protect and Recover WorkIf Excel says “Not Responding,” stop clicking and give it time to finish; it may be busy calculating,…
- Google Sheets Monthly Budget Template: Build One That WorksA Google Sheets monthly budget template is a spreadsheet for listing planned income and expenses, recording what you…
- Data Validation Greyed Out in Excel? Causes and FixesIn Excel for Microsoft 365 on Windows, Data Validation is usually greyed out because worksheet tabs are grouped,…
- Schedule Daily Email in Outlook: Options and StepsOutlook can schedule a message to send once, but it does not provide a built-in setting to repeat…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.