Excel Power Query: Keep Leading Zeros

Yes. To keep leading zeros in Excel Power Query, make the affected column’s data type Text before loading it. In Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Text/CSV, select Transform Data, then select the ID column in Power Query and choose Transform > Data Type > Text. This preserves values such as 00123 as text instead of converting them to the number 123.

Why leading zeros disappear

Power Query assigns data types to columns so it can work with them appropriately. An identifier made up of digits can look like a number, so an automatic type-detection step may classify it as a whole number. When that happens, 00123 becomes 123: numbers do not retain zeros at the left as part of their value.

Power Query often adds a step called Changed Type to the query. You can see the sequence of operations in the Applied Steps list in the Query Settings pane. If that step converts an ID, ZIP code, product code, or account number to a number, the zeros disappear from the result. It is a data-type issue, not a special display setting.

Whether a column should be text or numeric depends on what the values mean. A quantity such as 123 items is a number you may add or average. A code such as 00123 is generally a label: arithmetic on it makes no sense, and its exact characters matter. Treating codes as text is usually the right choice even when every character happens to be a digit.

Set the column to Text before loading

For a new text-file import in Excel for Microsoft 365 on Windows, use this workflow:

Go to Data > Get Data > From File > From Text/CSV and choose the CSV or other supported text file.
In the import preview, choose Transform Data rather than loading the preview directly.
In the Power Query Editor, select the column that contains the codes. To select more than one, hold Ctrl while selecting the column headings.
Choose Transform > Data Type > Text. If Excel asks whether to replace the existing type-conversion step or add a new step, replacing it is often the clearest option when the existing step is the one changing the codes.
Check a few values in the preview, then choose Home > Close & Load to return the results to Excel.

For a query you already created, select Data > Queries & Connections, open the query in the Power Query Editor, and change the column type there. If the type conversion is visible as a Changed Type step, you can edit or remove that step and set the column to Text. The preview updates as the steps are recalculated, so confirm that the original zeros return before loading.

The order matters: set the type before a conversion turns the value into a number. In a CSV that contains the literal characters 00123, removing the numeric conversion lets Power Query read those characters as text again. If the source itself already contains only 123, however, there are no stored zeros for Power Query to recover.

Check the source and the Applied Steps

When zeros are missing, inspect both the source and the query steps instead of immediately trying to format the worksheet. Open the source file in a plain-text editor if it is a CSV: if the file contains 00123, the source still has the zeros. If it contains 123, the loss happened before or during creation of that file. A spreadsheet program may have converted the value when someone entered or saved it.

Next, inspect Applied Steps from top to bottom. A type-conversion step may be called Changed Type or have a similar name if it was edited. Click the steps in sequence and check where the preview changes from 00123 to 123. If the value is intact at the source step but not after a conversion step, correct that conversion. If it is already absent at the source step, changing the later data type cannot restore the original characters.

Power Query may infer types for unstructured sources such as CSV files. Its inference can be unsuitable for identifiers, especially when a column mixes values or the first rows do not represent the full range of data. For a repeatable import, explicitly set important identifier columns to Text rather than relying on an automatic guess. On refresh, the query applies its saved steps again, so a deliberate text-type step helps keep the output consistent.

Why formatting the worksheet is not the same fix

Changing the loaded worksheet cells to the Text number format does not bring back zeros that Power Query already removed. By then, the query may have loaded the numeric value 123, and formatting that value as text produces the text “123,” not “00123.” Set the type in the query itself and refresh or reload the result.

A custom number format can make a numeric cell display a fixed number of digits. For instance, a format such as 00000 can show the number 123 as 00123. That is a display treatment: the underlying value remains the number 123. It may be useful for a report where a fixed-width appearance is enough, but it is not equivalent to preserving an ID as text. The zeros may not travel with the value when it is exported or used by another system.

There is another important source distinction. If an Excel source cell contains the text 00123, Power Query can preserve that text. If the source cell contains the number 123 and merely displays 00123 because of a custom number format, the underlying value is still 123. Do not assume a query will treat the worksheet’s visual formatting as part of the stored code. When leading zeros are meaningful, store the source value as text where possible.

When the zeros are already missing: padding and recovery

If you know every code must have a fixed width, you can add zeros back deliberately. This is reconstruction, not recovery: it is safe only when the required width is known and the source values have not become ambiguous. For example, if every code must have five characters, 123 can be reconstructed as 00123. If codes have varying lengths, padding every value to five characters could create incorrect identifiers.

In Power Query, choose Add Column > Custom Column and use an expression like the following, replacing ID with the actual column name. This is Power Query M language, not an Excel worksheet formula:

if [ID] = null then null else Text.PadStart(Text.From([ID]), 5, "0")

For an ID value of 123, this returns the text value 00123. The expression leaves nulls blank and converts non-null values to text before padding. It pads values shorter than five characters; it does not shorten values that are already longer. After checking the new column, you can remove or retain the original according to your reporting needs. If your codes have meaningful leading zeros but no fixed width, use the original text source rather than padding.

For an imported table where the source value is already stored as a number, changing its type to Text can only produce text from the number that remains; it cannot infer how many zeros were removed. Also be cautious with long digit strings. Excel numeric values support only 15 significant digits of precision, so a long identifier that has passed through a numeric cell may have lost information beyond its original leading zeros. Importing such identifiers as text from the outset avoids treating them as arithmetic values.

Keep the setting through refreshes and later steps

A Power Query query is a sequence of transformations. Once you set the column to Text in an appropriate step, later steps should not convert it back to a number. Review any subsequent type changes, merges, or appended data, especially if a refreshed file has different columns or values. A later automatic or manually added type-conversion step can undo the intended treatment.

After loading, refresh the query using Data > Refresh All and inspect a few representative codes. Include an example with leading zeros, a value without leading zeros, a blank if blanks are possible, and a longer value if the column allows one. This checks both that the query preserves the source and that the displayed result matches the format expected by the people or systems using it.

If the source changes over time, keep the same column meaning and format in each file. A query may refresh successfully while a source application has already converted codes to numbers before saving them. Power Query cannot reverse that upstream change. A useful handoff rule is to export identifiers as text and avoid opening and resaving CSVs in a way that silently changes their types.

Text versus number: choose by meaning

Choosing Text does not prevent every useful operation. Text values can be filtered, sorted, matched, and joined to other text identifiers. They are not appropriate for arithmetic, so keep measurements and quantities numeric. If a table contains both kinds of data, set the type column by column rather than making every field text merely to avoid one conversion.

Text sorting is alphabetical rather than numeric. For example, text values 1, 10, and 2 may sort in that order, while numeric values sort by magnitude. That difference is usually acceptable for codes, which are labels, but it can surprise someone who expects a text ID column to behave like a quantity. If you need a numeric sort order as well as an exact identifier, keep a separate numeric sort key only where the data rules make that conversion safe.

Postal or ZIP codes: use Text because zeros can be part of the code, and arithmetic is not meaningful.
Product, employee, or account IDs: use Text when exact characters identify a record.
Counts, prices, and measurements: use an appropriate numeric type when calculation is intended.
Fixed-width codes that have already lost zeros: pad only when the required width is known and consistent.

Frequently Asked Questions

Can Excel Power Query keep leading zeros from a CSV?
Yes, if the zeros are present in the CSV. Open it with Data > Get Data > From File > From Text/CSV, choose Transform Data, and set the relevant column to Text before loading.

Why does Power Query remove zeros even though the CSV has them?
An automatic or manual type-conversion step may be interpreting the column as a number. Find the conversion in Applied Steps and change the column to Text before that numeric conversion takes effect.

Can I add leading zeros with Excel cell formatting?
A custom number format can show zeros for a numeric value, but it does not make those zeros part of the stored value. For an identifier that must retain its exact characters, use Text in Power Query or store the source value as text.

Can Power Query restore zeros that were already removed?
Not by changing the type alone. If you know the exact fixed width, a Power Query padding step can reconstruct the expected string; otherwise, retrieve the original text from the source because the missing characters cannot be inferred reliably.

Will the zeros stay after I refresh the query?
They should, provided the query’s type step sets the column to Text and no later step converts it back to a number. Refresh the query and check sample values, and confirm that the source file still contains the zeros.

Should numeric-looking IDs be Text or Whole Number?
Use Text when the digits identify something rather than measure it. Use a numeric type for values you intend to calculate with. IDs can contain only digits and still be text data.

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.