Excel Power Query: Add Leading Zeros to Text

How-to basics · Updated November 21, 2026

To add leading zeros in Excel Power Query, create a custom column with Text.PadStart(Text.From([Code]), 6, "0"), replacing Code with your column name and 6 with the desired minimum width. The result is text: for example, the number 42 becomes 000042. Keeping the value as text is important because numeric cells do not retain leading zeros.

Why leading zeros disappear

Excel and Power Query treat numbers and identifiers differently. A numeric value represents an amount, so Excel stores 42 as 42; a display format can make it look like 000042 without changing the underlying value. An identifier such as a postal code, product code, or account number is usually better treated as text, because its digits are labels rather than quantities.

When Power Query reads a source, it may infer that a column contains numbers and assign it a numeric type. Converting a value such as 000042 to a number removes its leading zeros. Once that happens, the query cannot know whether the original text was 42, 042, or 000042. You can add zeros when you know the required width, but you cannot reliably reconstruct zeros that were never preserved in the source.

This distinction also explains why applying a custom number format in a worksheet is not the same as padding a value in Power Query. A number format changes how a numeric cell appears; it does not change the stored number or make the result a text identifier. Power Query padding creates text that retains the zeros when loaded into the worksheet.

Choose the right padding method

For a fixed-width code, use Text.PadStart. It adds the chosen character to the left until the text reaches the specified length. The length is a minimum, not a limit: a six-digit value stays six digits, while a seven-digit value remains seven digits rather than being shortened.

  • Fixed-width IDs: Use padding when every code should have at least a known number of characters, such as six digits.
  • Variable-width codes: Do not pad if different lengths carry meaning or if the source system expects the original text unchanged.
  • Visual formatting only: A worksheet custom number format may be enough if values are genuinely numeric and you only need them to look padded. It is not a substitute for text when exporting or matching codes.

For example, with a six-character target, 42 becomes 000042, 1234 becomes 001234, and 1234567 remains 1234567. Decide on the target width from the rules of the data, not simply from the longest value currently visible; future records may be longer.

Add leading zeros in the Power Query Editor

In Excel for Microsoft 365 on Windows, load or open the data in Power Query, then create a custom column. If the source is a worksheet table, select a cell in it and choose Data > From Table/Range. For a CSV file, use Data > Get Data > From File > From Text/CSV, then choose Transform Data to open the editor instead of loading immediately.

  1. In the Power Query Editor, select Add Column > Custom Column.
  2. Give the new column a clear name, such as Code (6 digits).
  3. Enter Text.PadStart(Text.From([Code]), 6, "0") in the custom-column formula box. Replace Code with the exact source-column name and set the width you need.
  4. Choose OK, then check several results, including values shorter than the target width and values already at or above it.
  5. Confirm that the new column has the Text data type. Select its type icon if necessary and choose Text.
  6. Choose Home > Close & Load to load the result into Excel. If you use a different load destination, choose the appropriate Close & Load option.

The expression uses Text.From to convert a numeric source value to text before padding. If the source column is already text, you can use Text.PadStart([Code], 6, "0"). Keeping the source column and adding a new padded column is often safer than replacing the original: it lets you compare the result and revisit the transformation later.

What the M expression does

Power Query formulas use the M language. In a custom-column dialog, enter the expression for the row being processed; Power Query builds the surrounding table step for you. A corresponding step in the Advanced Editor could look like this, assuming the previous step is named Changed Type and the source field is Code:

= Table.AddColumn(#"Changed Type", "Padded Code", each Text.PadStart(Text.From([Code]), 6, "0"), type text)

Text.From converts the field to text, Text.PadStart adds zeros at the left until the text has six characters, and type text declares the new column’s type. The function does not remove characters from a value that is already longer than six characters. It also does not add zeros to the right; for that, M has a separate function, Text.PadEnd.

If a column can contain blank or null values, decide how those should appear. To leave nulls empty while padding other rows, use this custom-column expression: if [Code] = null then null else Text.PadStart(Text.From([Code]), 6, "0"). If blank strings should also be treated specially, add a condition for an empty string based on the source data. Avoid silently turning missing identifiers into a string of zeros unless that is an explicit data rule.

Prevent type detection from removing zeros

The best point to protect identifier text is as early as possible in the query. Power Query often inserts an automatic Changed Type step after importing data. If the source contains text such as 000042 but that step converts the column to a whole number, the zeros are lost before your padding expression runs. In the Applied Steps list, inspect the type-conversion step. Remove or edit it, or select the identifier column and set its type to Text before other transformations depend on it.

When importing a CSV, check the preview and the query steps rather than assuming the visible preview proves the values will remain text. CSV files do not carry Excel cell formats or data types, so Power Query has to infer them. Setting the identifier column to Text in the query is more dependable than opening the CSV directly in Excel and relying on how Excel interprets it.

There is an important limitation: if the source file itself contains 42 rather than the text 000042, setting its type to Text preserves the characters that exist, but it cannot discover the missing zeros. In that case, padding is appropriate only if the intended width is known. For exceptionally long numeric-looking identifiers, import them as text from the start. Excel’s numeric precision is limited to 15 significant digits, so long codes should not be stored as numbers if every digit must be retained.

Common problems and how to fix them

  • The formula reports a type error: The column may contain values that are not compatible with the expression, or the column name may be misspelled. Check the exact name shown in Power Query and use Text.From([Code]) when values may be numeric.
  • The result still has no zeros: Confirm the target width is greater than the current text length. Padding to six characters does not change a value that already has six or more characters.
  • The zeros vanish after loading: Check that the new column is Text in Power Query and that Excel has not subsequently converted the loaded values to numbers. Text values should remain text in the output table.
  • Some rows show errors: Review nulls, error values, and mixed source content. Use an explicit null-handling expression where appropriate, and investigate source errors instead of replacing them indiscriminately.
  • Codes have decimals or scientific notation: That is often a sign that a code was interpreted as a number. For identifiers, use the original text source if available and set the type to Text before numeric conversion changes the value.

Be especially cautious with decimal values. A code such as 12.5 is not normally a meaningful identifier to pad, and converting numbers to text can produce a representation that does not match the source system’s intended code. Establish the data’s format first; do not use padding to conceal an incorrect type or a flawed import.

Refresh behavior and where the result goes

Power Query saves the transformation as query steps. When you refresh the query, it applies those steps to the current source data again, so new short codes receive the same padding rule. The query does not permanently edit the source file; it produces a transformed result that can be loaded to a worksheet table or, where appropriate, the data model.

After changing a query, use Data > Refresh All to refresh workbook connections, or refresh an individual query from its loaded table or the Queries & Connections pane. If new rows arrive, confirm that the source is a table or another range that includes them. A correct padding step cannot process rows that are outside the query’s source range.

Power Query is available in Microsoft Excel for Microsoft 365 on Windows. Excel for Mac also offers Power Query capabilities, but connector availability and parts of the interface can differ by version; use the available Get Data and query-editing commands in the installed release. Excel for the web is not a full replacement for desktop query authoring, so create or edit this transformation in Excel desktop when the required editor features are unavailable.

Power Query versus worksheet number formats

Choose Power Query when the padded result should be text in a repeatable import-and-refresh process, such as preparing a list of product codes for a lookup or export. The transformation is recorded and reapplied during refresh, which is useful when the source is updated regularly.

Choose a custom number format when the underlying data is numeric and only its worksheet display needs fixed-width zeros. For example, a format such as 000000 can make a numeric value display with six digits, but a formula or export may still use the underlying number 42. That difference matters when comparing identifiers, combining data from different systems, or writing a CSV for another application. If the zeros are part of the actual code, use text instead.

A worksheet formula can also create padded text, but it must be filled or maintained alongside the data. Power Query is generally the more convenient option when the values are imported and the same rule should run on every refresh; a worksheet formula may suit a small, manually maintained range.

Frequently Asked Questions

Can Power Query add zeros without changing the original column?
Yes. Use Add Column > Custom Column to create a separate padded column, leaving the source column intact. This is useful for checking results and keeping the original imported value available.

How do I make every code exactly six digits?
Text.PadStart pads values shorter than six characters, but it does not truncate longer ones. If values longer than six digits must be rejected or handled differently, add a separate validation step rather than assuming padding enforces an exact maximum length.

Can I add leading zeros to a number in Power Query?
Yes. Convert it to text in the expression with Text.From, then use Text.PadStart. The output is text, not a numeric value, which is normally the correct type for an identifier.

Why did Power Query remove zeros before my custom column?
An automatic type-detection step may have converted the source column to a number. Inspect Applied Steps and set the column to Text before that conversion, or remove the conversion if it is not needed. If the source already contains only the number 42, the original number of zeros cannot be inferred without a known width.

Will the zeros remain after refreshing the query?
Yes, provided the padding step remains in the query and the source values are available on refresh. Check that the output column stays typed as Text and that the query’s source includes any newly added rows.

Practical rule of thumb

For identifiers that must visibly and literally contain leading zeros, create a text column in Power Query and pad it to the documented width. Preserve the source column when possible, review automatic type changes, and test short, already-padded, long, and blank values before loading. That approach keeps the transformation reproducible while avoiding the common mistake of treating an identifier as a quantity.

Related

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.