Power Query Divide by Zero Error: How to Fix It Quickly

To fix a divide-by-zero error in Power Query, check whether the divisor is zero or null before dividing, and return a suitable result such as null. In Power Query Editor, select Add Column > Custom Column and use an M expression that tests the divisor first. This prevents a known zero denominator from producing an error while keeping other data issues visible.

What You’ll Need

An Excel workbook with the query that produces the error. In Excel for Microsoft 365 on Windows, open Data > Queries & Connections, then double-click the query to open Power Query Editor.
The names of the numerator and denominator columns, plus a decision about what a zero denominator should mean in your report: a blank/null result, zero, or a specific label.
A basic understanding that Power Query uses the M language. The expressions below go in Power Query, not in an Excel worksheet cell.

Step-by-Step

Find the step where the error first appears. In Power Query Editor, select the query in the Queries pane, then review Applied Steps on the right. Click the steps one at a time, starting near the end, until the preview shows the first error. If the preview already contains errors when the query opens, select a column with an error and inspect the Error details shown when you click a cell. This helps distinguish a division problem from a source error or an earlier conversion problem.
Identify the denominator and decide what the result should be. Look at the expression or step that calculates a ratio, rate, unit price, or percentage. For example, revenue divided by units has units as its denominator. A zero in that column makes the ratio undefined, so a blank is often more informative than zero. Returning zero says the ratio itself is zero, which may be misleading. Choose a result that fits the meaning of the calculation and downstream report.
Check the column types before changing the calculation. Select each input column and look at its type icon in the header, such as 123 for a whole number or 1.2 for a decimal. If a denominator contains text, a blank string, or an error, it is not the same as a numeric zero. Set columns to appropriate numeric types using the type icon or Transform > Data Type. If Power Query prompts you to replace the current step or add a new one, choose the option that preserves the sequence you need. Fix conversion errors separately rather than assuming the zero test will handle them.
Add a guarded custom column. Choose Add Column > Custom Column. Give the new column a clear name, such as Ratio, and enter an expression that checks the denominator before attempting division. For columns named Numerator and Denominator, the M expression is: if [Denominator] = null then null else if [Denominator] = 0 then null else [Numerator] / [Denominator]. Replace those sample names with the exact column names in your query. The first condition handles missing denominators; the second prevents division by zero; the final branch divides only when a usable nonzero denominator is present.
Choose a different fallback only if it matches your data rules. To return zero when the denominator is zero or null, use: if [Denominator] = null then 0 else if [Denominator] = 0 then 0 else [Numerator] / [Denominator]. This is appropriate only when zero is the intended business meaning. To return a readable text marker instead, such as Not applicable, use that text in place of null. Be aware that a text result can make the new column a mixed or text type, which may prevent numeric aggregation or charting later.
Review the result and set its type. Select the custom column and confirm that zero and null denominator rows show the chosen fallback while ordinary rows show the expected quotient. Set the result to Decimal Number for a ratio that may contain fractions. If it represents a rate, you can set the data type to Percentage, but check how the values display after loading. For example, 0.25 represents 25 percent; do not multiply by 100 as well as applying percentage formatting unless the source calculation requires it.
Load and refresh the query. Choose Home > Close & Load, or Home > Close & Load To if you need to choose the destination. Refresh the query and confirm that the error no longer returns. In Excel for Microsoft 365 on Windows, use Data > Refresh All to refresh workbook queries. If the source data changes regularly, refresh again after a new batch arrives to ensure the guard still handles newly introduced zero or missing values.

If you already have a calculated column that can produce several kinds of errors, a broader fallback is possible. With the calculation in a custom column, use try [Numerator] / [Denominator] otherwise null. This returns null for any error in that calculation, not just division by zero. Use it only when hiding all errors from the expression is acceptable; otherwise, test for zero and null explicitly so unrelated problems remain detectable.

Common Mistakes

Replacing every error with zero without checking its cause. An error may come from text that cannot convert to a number, a missing field, or a source issue. Replacing all of those with zero can make a report look complete while quietly changing its meaning. Prefer a specific zero/null condition, and investigate errors that remain.
Testing the numerator instead of the denominator. Division fails when the value after the division operator is zero. Check the denominator column used in that exact calculation, not a related field with a similar name.
Confusing null, blank text, and zero. A true Power Query null, an empty text value, and the number 0 are different values. A test for [Denominator] = 0 does not convert a text value such as an empty string into a number. Set types and clean the source consistently before calculating.
Using the worksheet formula syntax in the Custom Column box. Power Query expects an M expression, not an Excel formula beginning with an equals sign. For instance, use the conditional expression shown above in the Custom Column dialog; do not enter an Excel IF formula there.
Returning text in a column meant for arithmetic. A label such as Divide by zero can be useful for inspection, but it is not numeric. If you need to sum or average the output, return null or another numeric value and keep diagnostic text in a separate column.
Fixing the wrong Applied Step. If a type conversion or merge creates the error before your division step, editing the final calculation alone may not solve it. Find the earliest step where the error appears and address that cause first.

Tips

Keep the original numerator and denominator columns in the query until you have validated the result. That makes it easier to trace an unexpected value back to its inputs. Give the result a descriptive name, such as Revenue per Unit, rather than leaving it as Custom or Custom.1. If a report needs to distinguish an undefined ratio from a missing input, consider adding a separate status column rather than using one fallback for both cases.

For a simple zero-denominator case, the explicit conditional is easier to audit than a general error handler. It documents the rule directly and lets unrelated conversion or data errors surface. Conversely, try ... otherwise can be useful when the intended policy really is to suppress any error from one small calculation. Avoid wrapping an entire query in a broad error handler, since that can mask problems across many rows or fields.

Column names in M must match the query exactly, including spaces and punctuation. If a column is named Sales Amount, Power Query typically refers to it as [Sales Amount]. Use the field list in the Custom Column dialog to insert a column reference if you are unsure of its spelling. When you edit a query through Home > Advanced Editor, preserve the existing let/in structure and commas between steps; a missing comma can create a new syntax error unrelated to division.

Power Query Editor in Excel for Mac may have a different set of controls depending on the Microsoft 365 build. The M expression itself is the same. Use the available Data or Get Data query options to open the editor; if a specific button is not shown, use the equivalent query editing control available in that build. Excel for the web has more limited Power Query editing capabilities than Excel for Windows, so edit the query in the desktop app if the needed editor controls are unavailable.

Troubleshooting

The custom column still shows an error. Confirm that the expression tests the actual denominator and that its column name is exact. Check whether the denominator contains text, a cell-level error, or a value that failed conversion before reaching the conditional. Select an error cell and read its details. Apply the appropriate type or source cleanup step, then refresh the preview.
The error appears before the division step. Click earlier Applied Steps until you find where the error first appears. If it begins at Changed Type, inspect the source values that cannot be converted. Correct the conversion or handle those values before calculating the ratio. If it starts at a merge or custom column, inspect that operation rather than only modifying the final step.
Null values are not behaving as expected. Verify that the cells contain Power Query nulls and not empty text or spaces. Trim or clean text columns as appropriate, then convert them to a numeric type. Do not treat every conversion problem as a zero; decide whether invalid or missing inputs should remain null, be flagged, or be excluded.
The result displays as a decimal instead of a percentage. Confirm that the result is numeric, then select Percentage as its type or format it as a percentage after loading to Excel. A stored value of 0.4 corresponds to 40 percent. If it appears as 0.4%, check whether the source calculation was scaled unexpectedly.
Refresh brings the error back. A later source file may contain new zeros, nulls, text, or a changed column type. Refresh, inspect the failing row and its inputs, and confirm the guard step is still applied after the latest type-cleaning step. If columns were renamed upstream, update the references in the custom column.

Frequently Asked Questions

What does a divide-by-zero error mean in Power Query?
It means a calculation tried to divide a value by zero. The denominator may be an actual numeric zero, or an earlier transformation may have produced a value that needs investigation.

Should I replace the error with zero or null?
Use null when the ratio is undefined or unavailable and you want to avoid implying a real zero result. Use zero only if your reporting rules specifically define a zero denominator as a zero output.

Can I remove the error with Replace Errors?
Yes. Select the affected column and use Transform > Replace Errors in Power Query Editor to replace existing error values with a chosen value. This handles errors already produced, but a guarded calculation is clearer when you know the denominator can be zero. Replacing errors broadly may hide unrelated problems.

Does try otherwise catch only divide-by-zero errors?
No. It catches errors raised by the expression inside try, including issues such as invalid numeric operations. Use an explicit denominator test when you want to handle zero or null but keep other errors visible.

Can I fix this in the Excel worksheet instead?
You can add worksheet-level error handling after loading, but it does not correct the Power Query result or its refresh logic. Fixing the calculation in Power Query is usually more reliable when the error is generated during query refresh.

Will the fix keep working after a refresh?
Yes, if the relevant columns and types remain consistent and the guard step is still in the query. Refresh with representative new data, especially after source files or column names change, to verify that the rule still applies.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.