Power Query Remove Leading Zeros Without Losing IDs

Yes. In Power Query, use Text.TrimStart to remove leading zero characters from a text value; for example, it changes 000123 to 123. In Excel for Microsoft 365 on Windows, add a custom column with if [Code] = null then null else Text.TrimStart(Text.From([Code]), {"0"}), then use that column in your output. Keep in mind that removing zeros from an identifier can change its meaning.

What removing leading zeros means

Leading zeros are zero characters at the start of a value, before its first nonzero character. Removing them is different from deleting every zero. For example, trimming leading zeros changes the text value 0005070 to 5070; it does not change it to 57. Zeros in the middle or at the end remain untouched.

Power Query is Excel’s data import and transformation tool. Its steps are recorded in a query and run again when the query refreshes. That makes a Power Query transformation useful when new files arrive regularly: you can apply the same cleanup each refresh rather than manually editing the cells again.

The important distinction is whether the column represents a number or a label. A value such as a quantity may be numeric, so the text 0008 and the number 8 can be treated as equivalent for calculations. A postal code, employee number, product code, or account number is usually an identifier. Its zeros may be part of the expected label, so removing them may make the value incorrect even if it looks neater.

Remove leading zeros with a custom column

In Excel for Microsoft 365 on Windows, open the query in Power Query Editor. If the query is already loaded, open Data > Queries & Connections, then double-click the query. Select Add Column > Custom Column and give the new column a clear name, such as Code without leading zeros. With the original values in a column named Code, enter this expression:

With the source value in a column named Code, the custom-column expression is:

if [Code] = null then null else Text.TrimStart(Text.From([Code]), {"0"})

Check that the input column contains the values you intend to clean. Text values such as 000123 retain their zeros until this transformation runs.
Choose Add Column > Custom Column and enter the expression above. Replace Code with the actual column name, keeping the square brackets.
Inspect the preview. For text input, 000123 should become 123, and 12003 should stay 12003.
Remove the original column if it is no longer needed, or keep it for comparison. Rename the new column if you want it to take the original column’s place.
Choose Home > Close & Load to return the result to Excel. On the next refresh, Power Query applies the recorded step to the refreshed data.

The expression converts the value to text before trimming so the function receives text. The null check leaves missing values blank rather than trying to process them. If your column name contains spaces, use its exact name inside the brackets, such as [Account ID].

What Text.TrimStart does

Text.TrimStart is a Power Query M function that removes specified characters from the beginning of a text value. The second argument, {"0"}, is a list containing the character to trim. It means “remove zero characters from the start,” not “remove the character zero everywhere.” The function continues trimming while the first character matches the supplied character.

For example, these results illustrate the behavior:

00045 becomes 45.
000 becomes an empty text value, because every character is trimmed.
004020 becomes 4020; the zero between the 4 and 2, and the final zero, remain.
AB0007 remains AB0007, because its first character is not a zero.

This function is character-based. It does not decide whether a string is a valid number, and it does not understand that some codes have a required length. That is why you should preview the result and choose a rule that matches the data rather than assuming every zero at the front is disposable.

Handle all-zero values and nulls deliberately

One edge case deserves attention: an input consisting only of zeros becomes an empty string. That is different from a null value, which represents missing data. If 000 means the numeric value zero in your dataset, you may prefer to return 0 rather than a blank. Use this custom-column expression to preserve a single zero for all-zero text values:

With the source value in a column named Code, use this expression when an all-zero value should remain 0:

if [Code] = null then null else let cleaned = Text.TrimStart(Text.From([Code]), {"0"}) in if cleaned = "" then "0" else cleaned

This version keeps null as null, trims leading zeros from other values, and changes an empty result to the text value 0. The output is still text. If the column is an identifier, that is often appropriate; if you need a number for arithmetic, convert it only after deciding how blanks and invalid values should be handled.

Do not apply this all-zero rule automatically to every dataset. In an identifier system, 000 could be a meaningful code, a placeholder, or an invalid record. Confirm the intended meaning with the data specification or source owner before changing it.

Check data types before transforming

Leading zeros are only available to remove if they still exist in the value Power Query receives. If the source contains the text 000123, the zeros are present. If the value was already converted to the number 123 before Power Query reads it, there is nothing left to trim. Changing that number’s display format in Excel may show zeros again, but that is formatting, not recovery of the original text.

For codes, set or preserve the column as Text as early as practical in the query. In Power Query Editor, select the column and choose Transform > Data Type > Text. Review the Applied Steps list as well: an automatically inserted Changed Type step may have converted a column before your cleanup step. If the source reader has already interpreted a field as a number, changing its type later cannot recreate the original leading zeros.

For genuine quantities, a numeric result may be useful. After trimming, you can set the column’s type to a whole number if every value is a valid whole number and you want to calculate with it. Avoid converting codes merely to make zeros disappear: a numeric type can remove formatting distinctions and may not be suitable for values containing letters, punctuation, or more digits than the numeric type can safely represent.

Choose the right rule for identifiers

Removing zeros is not always the best cleanup. Some systems require a fixed width. A five-character code 00127 may be different from the shorter code 127, even though both could be interpreted as the number 127. If you strip the zeros and later need the original width, you must know that width and deliberately pad the value again. Without a documented rule, the transformation is potentially irreversible.

Before applying the change, consider these questions:

Is the field used for arithmetic, or is it a label used to match records?
Does the source system specify a fixed number of characters?
Are leading zeros significant in the source files, database, or downstream import?
Do any rows contain only zeros, blanks, nulls, spaces, or nonnumeric characters?
Will the cleaned value be used as a join key? If so, do the other tables use the same zero-removal rule?

For a join, consistency matters more than appearance. If one table stores 000123 and another stores 123, trimming both sides may make them match, but it may also collapse distinct identifiers. Check for duplicates after cleaning: two original values can become the same output value. Keeping the original column temporarily makes that review easier.

Common mistakes and troubleshooting

A frequent mistake is using a broad replace operation to remove zeros. Replacing every 0 with nothing would damage values such as 12003, not just remove the prefix. Text.TrimStart is more precise because it only examines characters at the start of the text.

Another common issue is trimming spaces instead of zeros, or vice versa. Power Query’s Trim formatting option is intended for whitespace around text; it does not mean “remove leading zeroes.” If values contain spaces before their zeros, first decide whether those spaces are unwanted. You can trim whitespace separately, then remove zeros, or handle both explicitly in a custom expression. Avoid silently cleaning spaces if they could be meaningful in your source.

If the custom column shows an error, check that the column name matches exactly and that the values are convertible to text. The expression above handles nulls, but unusual structured values or errors already present in the source may require separate handling. If the preview shows no change, inspect the actual first character: it may be a space, a different zero-like character, or a value that has already been converted to a number.

After changing the query, refresh it and inspect the loaded result, not just the editor preview. Also check the query’s Applied Steps order. A later type conversion or another transformation could alter the output after the trim step. If the result feeds formulas, merges, or reports, verify those dependent outputs too.

Power Query versus an Excel formula

A worksheet formula can also remove leading zeros, but it behaves differently in a workflow. A formula is recalculated in worksheet cells and must be filled or spilled across the relevant range. A Power Query step transforms data during import and refresh, which is often easier to maintain for recurring file cleanup. The choice depends on where the source data lives and when the cleanup should happen.

Power Query is a strong fit when you import or combine files repeatedly, want a recorded sequence of transformations, or need the same cleanup to run on refresh. A worksheet formula may be more convenient when the data is already in a sheet and you want an immediately visible, cell-by-cell result. Neither approach can determine whether leading zeros are meaningful without a rule from the data context.

There is also a difference between removing zeros from a value and changing how a value appears. A numeric cell can be displayed with a custom number format that pads it with zeros, while its underlying value remains numeric. Conversely, converting a text code to a number can remove those zeros from the stored result. Power Query’s trim operation changes the text value it outputs; it is not merely a display setting.

Frequently Asked Questions

Can Power Query remove only zeros at the beginning of a value?
Yes. Use Text.TrimStart with {"0"} as the trim-character list. It removes consecutive zero characters at the start and leaves zeros later in the value unchanged.

Why did Power Query remove my leading zeros before I used Text.TrimStart?
The column may have been converted to a number by the source or by an earlier Changed Type step. Numbers do not retain leading zeros as part of their value. Preserve or import identifier columns as text before that conversion if the original characters matter.

What happens to a value such as 0000?
The basic trimming expression returns empty text because all its characters are leading zeros. If the value should represent zero, use an explicit all-zero rule to return 0, or choose a different treatment based on the field’s meaning.

Will this change the source file?
No. Power Query creates transformed output for the query; it does not edit the original source file. The cleanup is applied again when the query refreshes. If you need a permanently changed source file, export or save the transformed results separately.

Can I keep the original column as well as the cleaned one?
Yes. Add a custom column instead of replacing the original, then compare the two columns and check for duplicates or unexpected blanks. Once you are satisfied with the results, decide whether the original should remain in the loaded table.

Practical takeaway

For text data, the core Power Query operation is Text.TrimStart with the zero character specified. Build it as a custom column, protect nulls, inspect all-zero values, and confirm that zeros are not meaningful parts of an identifier. Keeping the source column during validation and checking the result after refresh helps catch type changes, duplicate keys, and other problems before the cleaned data is used elsewhere.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.