In Excel Power Query, select the column and choose Transform > Data Type > Whole Number to remove leading zeros from numeric values. If the values are identifiers that must remain text, use a custom column with Text.TrimStart instead; converting an identifier to a number can damage it.
What removing leading zeros means
Leading zeros are zero characters at the start of a value, such as the first three characters in 000482. Removing them makes that value display as 482. Zeros within a value are different: 000402 becomes 402, not 42. Power Query can either interpret a column as numbers or remove only its leading zero characters while keeping the result as text. Which method is appropriate depends on what the values represent.
A quantity such as a count is usually numeric, so converting it to a whole number is a straightforward solution. A postal code, product code, account number, or employee ID is usually an identifier. Even if it contains only digits, it may need to stay text because its length and exact characters carry meaning. Decide what the column means before changing its type.
Remove zeros by converting the column to a number
Use this method when every value is genuinely numeric and you want Power Query to return numbers, not text. In Excel for Microsoft 365 on Windows:
- Open the query: choose Data > Queries & Connections, then double-click the query, or create one with Data > From Table/Range.
- In Power Query Editor, select the column containing the values.
- Choose Transform > Data Type > Whole Number. If values can have a fractional part, choose Decimal Number instead.
- Review the preview for errors, then choose Home > Close & Load to return the results to Excel.
For example, text values 0007, 0120, and 900 converted to whole numbers become 7, 120, and 900. The zeros disappear because numbers do not store leading zeros as part of their value. When you refresh the query, Power Query repeats the conversion on the incoming data.
Power Query adds a step to the Applied Steps list, commonly named Changed Type. You can select that step to inspect what it does, or remove it with the step’s delete control if it inferred the wrong type. The column’s data type icon also indicates whether Power Query treats it as text, a whole number, or another type.
Keep the result as text and trim zeros only
For codes that should stay text, add a custom column rather than converting the original column to a number. In Power Query Editor, choose Add Column > Custom Column, give the new column a name, and enter this expression, replacing Code with the actual column name:
if [Code] = null then null else let cleaned = Text.TrimStart(Text.From([Code]), {"0"}) in if cleaned = "" then "0" else cleaned
This expression preserves nulls, removes zero characters only from the beginning, and keeps the output as text. For instance, 000482 becomes 482, 000402 becomes 402, and 004B becomes 4B. A value consisting entirely of zeros, such as 000, becomes 0 rather than a blank. If an all-zero value should instead become blank, change the final "0" to "".
To replace the original column, create and check the custom column first. Then remove the old column, rename the new one to the original name, and confirm its data type is Text. This makes the transformation easy to review before discarding the source field.
Choose the right method for the data
Think about how the result will be used, not just how the source looks. A column of digits is not automatically a column of numbers: many identifiers contain digits because that is how a system labels records. Use the following guide:
- Counts or measured values: Convert to Whole Number or Decimal Number so Excel can calculate with them.
- Product, employee, or account codes: Keep as text and trim the leading characters if the business rules say those zeros are not significant.
- Postal codes or phone numbers: Usually keep as text. Their formatting and length may matter, and arithmetic is not meaningful.
- Mixed values: Keep the column as text if some entries include letters or punctuation, or handle the different cases explicitly before conversion.
Consider an ID such as 000015. If it identifies a record, converting it to 15 may make it look unlike the ID used by another system, and it may no longer match a lookup key. Conversely, if the value is a count of 15 items, storing it as text may make totals and numeric comparisons inconvenient. The correct result depends on the field’s purpose.
Prevent automatic type detection from changing values
Power Query may add a Changed Type step automatically, particularly when it detects data types in a source. If a code is interpreted as a number at that stage, its leading zeros are lost. Changing the type back to Text later does not bring those zeros back: the query only has the number that remains, such as 15, not the original text 000015.
When the original characters matter, set the column to Text as early as possible, before any step converts it to a number. Inspect the Applied Steps list for Changed Type. If that step has already removed the zeros, delete or edit it and return to the source step, then set the column to Text. For a CSV or other text source, check the preview and type-detection steps before loading the result.
If the source itself already stores the field as a number, Power Query cannot infer how many zeros were originally present. You need a reliable rule, such as a known six-character ID width, or the original text from the source system. Adding zeros back to a fixed width is a separate transformation; it is not the same as recovering lost information.
Common issues and ways to avoid them
- Conversion errors: A column may contain letters, blanks represented by unusual text, or punctuation. Whole Number conversion expects values it can interpret as integers. Check the error details and decide whether to clean, preserve, or exclude those entries rather than silently treating them as valid numbers.
- Long numeric-looking IDs: Excel numeric values retain only 15 significant digits of precision. Do not convert a long identifier to a number if every digit must remain exact; keep it as text.
- Zeros are still visible in Excel: A number format can display leading zeros even though the stored value is numeric. Power Query’s numeric conversion removes zeros from the value, but a later cell format may display them again. Check the loaded cell’s value and number format before assuming the query did not work.
- Whitespace around values: Spaces are not zeros. If a value has leading spaces before its zeros, trim whitespace first or account for it in a separate cleaning step;
Text.TrimStartwith{"0"}removes zero characters, not spaces. - Zeros inside a value disappear: Avoid replacing every zero or using a broad text replacement. Trimming from the start removes only the initial run, so
1002remains1002.
Refresh behavior and the loaded worksheet
Power Query transformations are recorded as steps in the query. After the query is loaded, choose Data > Refresh All to apply those steps to updated source data. The query output is separate from the original source: changing a value in the loaded result table is not a durable substitute for changing the query, because a refresh can overwrite the output.
For a text-trimming transformation, check that the result column remains Text after refresh. For numeric conversion, check that it has the intended numeric type and that the source does not introduce new nonnumeric entries. If other queries depend on this query, changing a column’s type or name may affect those later steps, so refresh and review dependent results as well.
Excel for Mac and other versions
The menu paths in this guide are for Excel for Microsoft 365 on Windows. Power Query is also available in Microsoft 365 for Mac, but the available connectors and editor features can vary by Excel version and update channel, and menu placement may differ. If you do not see the same command, look for the query editing and data type controls in the Power Query Editor available in your build. Excel for the web does not provide the same full Power Query editing experience as the Windows desktop application.
The M expression shown for trimming zeros is a Power Query expression, not an Excel worksheet formula. It belongs in the Custom Column dialog in Power Query Editor. You do not enter it into a worksheet cell, and the worksheet functions for formatting numbers are not substitutes for a query transformation when the output must be cleaned on every refresh.
Frequently Asked Questions
Can Power Query remove leading zeros without converting to a number?
Yes. Add a custom column using Text.TrimStart and keep the result as Text. This is generally the safer approach for codes and identifiers that must retain their exact characters.
Why did changing the column back to Text not restore the zeros?
The zeros were discarded when the value was first interpreted as a number. Text conversion can turn the remaining number back into text, but it cannot know whether the source was originally 15, 015, or 000015.
Does Power Query Trim remove zeros?
No. Text trimming removes whitespace at the start or end of text. To remove zero characters specifically, use Text.TrimStart with a list containing "0".
How do I keep a fixed number of digits?
Do not remove zeros if the required result is a fixed-width code. Keep the column as text and use a separate padding rule based on the required width. Confirm the correct width from the source system or data specification; it cannot be safely guessed from the values alone.
Will the zeros stay removed after I refresh?
Yes, provided the cleanup is a step in the query and the refreshed source values follow the assumptions of that step. Refresh the query and check for new errors or values that need different handling.
Related
- Wedding Budget Spreadsheet: Free Online Options and Setup
- Excel Power Query: How to Split Text into Separate Columns
- Wedding Budget Planner Spreadsheet Free: How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Restrictions ExplainedExcel data validation restrictions control what users can enter in selected cells: choose the cells, then go to…
- Analysis Report in Excel: How to Build a Useful ReportTo make an analysis report in Excel, organize clean records in a table, summarize them with a PivotTable…
- Excel Power Query Connection Only ExplainedIn Excel Power Query, Connection Only saves a query in the workbook without loading its results into a…
- Abby Lawson Budget Spreadsheet: Is It Free?Yes—Abby Lawson has offered free budgeting resources, and a spreadsheet attributed to her may be available without a…
- BI Conditional Formatting in Power BI: Rules and UsesBI conditional formatting uses color, icons, data bars, or other visual cues to make important values in a…
- Track Changes in Word 365: A Practical How-To GuideTo track edits in Word 365, open your document, go to Review > Track Changes, and choose whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.