Calculated Field Builder
Configure two source fields, test a sample row, and create a ready-to-paste custom column expression.
Sample Calculated Fields
| Field A | Field B | Operation | Calculated Value | Purpose |
|---|---|---|---|---|
| 125 | 0.15 | Multiply | 18.75 | Calculate a fee or tax amount. |
| 120 | 150 | Percent Change | 25.00% | Compare current and earlier values. |
| North | Hardware | Join Text | North - Hardware | Create a readable reporting label. |
| 105 | Approved | Conditional | Approved | Return a status when a threshold is met. |
How the Expression Is Built
Every custom column starts with a table step, a field name, an each expression, and a data type.
For numeric work, the calculator uses try Number.From(...) to handle values that cannot be converted. Division and percent formulas also check for zero denominators. Text joining uses Text.Combine with the selected separator. Conditional rules use if ... then ... else.
How to Use This Calculator
- Enter the exact name of the query step before the calculated field.
- Name the new field clearly, such as Net Amount or Product Label.
- Select an operation that fits the two source fields.
- Provide sample values to check the expected output.
- Choose how blanks or conversion errors should behave.
- Set a separator for text or a threshold for conditional results.
- Select Build Calculated Field and review the preview above the form.
- Copy the M expression into the Custom Column dialog in Power Query.
Calculated Fields for Better Queries
Calculated fields turn existing columns into useful new values. They help analysts avoid manual work. A clear formula also makes a query easier to review. Power Query stores each step. That history makes transformations repeatable across refreshed files.
A calculated field can add prices, compare periods, or join text labels. It can also create flags for later filtering. The most useful formula is usually simple. Give the new column a descriptive name. Choose source fields that already have dependable values.
Numeric formulas should consider blanks and errors. A division formula needs special care. A zero denominator should not create a misleading result. You can return null instead. Null keeps the row available while showing that a value could not be calculated. The generated expression in this tool includes a safe pattern for division.
Text calculations need different handling. Numbers and dates should be converted to text before joining them. This prevents type errors during refresh. A separator makes combined labels easier to scan. For example, a region and product field can create a compact reporting label.
Conditional formulas help classify records. You can compare a source value with a threshold. Then return another value or null. This supports simple bands, quality checks, and workflow flags. Keep the rule visible in the query. Future editors should understand why the condition exists.
Power Query uses the M language. A custom column is commonly added with Table.AddColumn. The table step comes first. The new column name comes next. Then each defines the calculation for every row. A final type declaration protects later calculations. Number type suits numeric output. Text type suits combined labels.
Use the preview result as a quick check. It represents one pair of sample values. It does not replace testing with real source rows. Test positive, negative, blank, and zero values. Also test changed column types. Good testing prevents refresh failures after a new file arrives.
Keep column names stable whenever possible. Renaming a source column can break later steps. When a source must change, update the calculated expression immediately. Check Applied Steps after editing. The error view can show affected rows. Fix the earliest failing step first.
A well named calculation improves reporting. Names like Net Amount, Margin Rate, and Order Label explain themselves. Avoid vague names such as Result or New Column. Clear names help teammates build pivots, models, and charts correctly. They also reduce duplicate transformations.
This calculator creates a starting expression. Paste it into a Custom Column dialog. Adjust the previous-step name when needed. Then set the output type in Power Query. Refresh the query and inspect the new field. Small, deliberate steps make complex data preparation manageable.
Before publishing, document the business meaning behind the formula. Note the expected units. Record any null rule. This protects users when source systems change. Teams make better decisions each day. Documentation keeps refreshes safe and easy to audit.
Frequently Asked Questions
1. What is a calculated field in Power Query?
It is a new column created from existing row values. The expression runs for each row when the query refreshes. You can use it for arithmetic, labels, flags, comparisons, and cleanup tasks.
2. Where do I paste the generated expression?
Open Add Column, choose Custom Column, enter the new column name, and paste the expression after each. You can also use the full Table.AddColumn line in the Advanced Editor.
3. Why does a calculation return null?
Null usually means a source value was blank, could not become a number, or failed a safeguard. It can also appear when a division denominator is zero or a conditional test is not met.
4. How does the zero handling option work?
It changes missing or invalid numeric values to zero before calculation. This is useful for totals. Use it carefully because zero may have a different business meaning than a missing value.
5. Can I combine text and numbers?
Yes. Select Join Text. The M expression converts source values to text and places your separator between them. This is useful for labels such as region, product, order, or account codes.
6. What does percent change calculate?
It calculates ((second value minus first value) divided by first value) times 100. The formula returns null when the first value is zero because a percentage change from zero is undefined.
7. Why should I set a data type?
Types keep later steps predictable. A numeric type supports totals and averages. A text type supports labels. Setting the type early reduces errors when the query loads into models or reports.
8. Will renamed columns break my field?
Yes, a later expression can fail if it references an old column name. Update the calculated field after renaming source columns. Review Applied Steps to find the first step with an error.
9. Can I use this for more complex rules?
Yes. Start with the generated structure, then add nested conditions, date functions, or lookup logic in the Custom Column dialog. Test each change with several rows before loading the query.
10. What is the difference between a preview and query output?
The preview uses only the sample values entered here. The query output evaluates every row from your actual data source. Test the final formula with real rows, including blanks and errors.
11. Should I use null or zero for missing values?
Use null when the value is unknown or inapplicable. Use zero only when zero is a valid substitute. This distinction keeps totals, averages, and business conclusions accurate during refresh.