Keep or remove leading zeros in Power Query
How-to · By Tahir Nas · Examples run in our simulator · Updated
9 min read
To keep leading zeros in Power Query, set the column's type to Text before any step converts it to a number, because a number has no leading zeros to keep. If the zeros are already gone, put them back with Text.PadStart(Text.From([Code]), 6, "0"). To remove them on purpose, use Text.TrimStart([Code], "0") and decide what an all-zero code should become.
Why the zeros disappear
A leading zero is part of how a code is written, not part of a number's value. The number 123 and the code "000123" look similar, but only the code has six characters. Once a column becomes a number, the zeros are gone and nothing in the number records how many there were.
The usual cause is a step you didn't write. Microsoft's data types article (Microsoft Learn) explains that for unstructured sources such as CSV and Excel files, Power Query detects column types from the first 200 rows and adds a Changed Type step. A column of order numbers made only of digits looks numeric, so it becomes Whole Number:
let
Source = #table({"Order No", "Qty"}, {{"000123", "5"}, {"004560", "12"}, {"078900", "3"}}),
Detected = Table.TransformColumnTypes(Source, {{"Order No", Int64.Type}, {"Qty", Int64.Type}})
in
DetectedResult
| Order No | Qty |
|---|---|
| 123 | 5 |
| 4560 | 12 |
| 78900 | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The order numbers come out as 123, 4560 and 78900. Nothing failed, so nothing warns you. The zeros are simply gone from this step onwards.
Keep leading zeros: type the column as Text
The fix is to change that step, not to add a repair after it. In the editor, select the Changed Type step, click the type icon in the code column's header and choose Text. When Power Query asks whether to replace the current conversion or add a new step, choose Replace current: a new step would run after the zeros are already gone. In M, the column's type in that step becomes type text:
let
Source = #table({"Order No", "Qty"}, {{"000123", "5"}, {"004560", "12"}, {"078900", "3"}}),
Typed = Table.TransformColumnTypes(Source, {{"Order No", type text}, {"Qty", Int64.Type}})
in
TypedResult
| Order No | Qty |
|---|---|
| 000123 | 5 |
| 004560 | 12 |
| 078900 | 3 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now Order No keeps "000123", "004560" and "078900", while Qty is still a whole number. The rule of thumb: if you would never add, average or sum a column, it is an identifier, and identifiers are text. The lesson choose types deliberately: identifiers are text covers the rule in more depth, and the guide to data type pitfalls lists other ways an automatic type quietly changes values.
Importing a CSV with codes
The same applies when you import a CSV. Keep the promoted headers, then set the types yourself:
let
Csv = Csv.Document("Order No,Qty#(lf)000123,5#(lf)004560,12", [Delimiter = ",", Columns = 2, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Csv, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"Order No", type text}, {"Qty", Int64.Type}})
in
TypedResult
| Order No | Qty |
|---|---|
| 000123 | 5 |
| 004560 | 12 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The order numbers arrive as "000123" and "004560". In a real query Csv.Document reads the file's contents rather than inline text, but the type step is the same. If you'd rather Power Query never guess types for a workbook, the same Microsoft article shows where to turn off type detection in the Options dialog, for the current file or for every new file. Other CSV surprises, such as line breaks and delimiters inside values, are covered in CSV quirks in Power Query.
If the source is an Excel sheet where the zeros were already lost before Power Query saw them, no type setting brings them back. Excel stored a number. That is a job for padding.
Put lost zeros back with Text.PadStart
When you know the code has a fixed width, padding restores it. Text.PadStart pads text to a given length with a chosen character (Text.PadStart, Microsoft Learn). Because it works on text, convert the number with Text.From first.
let
Source = #table({"Order No"}, {{123}, {4560}, {78900}, {1234567}, {null}}),
Padded = Table.AddColumn(Source, "Order Code", each if [Order No] = null then null else Text.PadStart(Text.From([Order No]), 6, "0"), type nullable text),
Formatted = Table.AddColumn(Padded, "With D6", each if [Order No] = null then null else Number.ToText(Int64.From([Order No]), "D6"), type nullable text)
in
FormattedResult
| Order No | Order Code | With D6 |
|---|---|---|
| 123 | 000123 | 000123 |
| 4560 | 004560 | 004560 |
| 78900 | 078900 | 078900 |
| 1234567 | 1234567 | 1234567 |
| null | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Two things to notice. First, 1234567 is already longer than six characters, and padding leaves it as it is rather than cutting it. If a value is longer than your expected width, that is a data problem to look at, not one to hide. Second, the null row stays null because the if handles it before any conversion.
The With D6 column shows the alternative: Number.ToText with the "D6" format. Microsoft's standard numeric format strings page (Microsoft Learn) says the D specifier pads with zeros to the minimum number of digits and is supported only for integral types, which is why the example converts with Int64.From first. The same page points to Text.PadStart for padding a result string, and for codes it is the simpler choice.
The site's Text.PadStart reference has more tested examples, and the exercise build fixed-width badge numbers practises exactly this.
Only pad when the width is fixed
Padding assumes every code has the same width. That holds for many order numbers and account codes. It doesn't hold for UK phone numbers, where the rule is about a missing leading 0, not a fixed width of zeros. The post on cleaning phone numbers and postcodes handles that case with its own rule.
Remove leading zeros with Text.TrimStart
Sometimes you want the opposite: a system exports "000123" but the other system stores "123". Text.TrimStart with "0" as the trim character removes zeros from the start (Text.TrimStart, Microsoft Learn).
let
Source = #table({"Account"}, {{"000123"}, {"0045"}, {"0000"}, {"12"}, {null}}),
Trimmed = Table.AddColumn(Source, "Trimmed", each Text.TrimStart([Account], "0"), type nullable text),
Safe = Table.AddColumn(Trimmed, "Safe", each if [Account] = null then null else if [Trimmed] = "" then "0" else [Trimmed], type nullable text),
AsNumber = Table.AddColumn(Safe, "As number", each Number.FromText([Account]), type nullable number)
in
AsNumberResult
| Account | Trimmed | Safe | As number |
|---|---|---|---|
| 000123 | 123 | 123 | 123 |
| 0045 | 45 | 45 | 45 |
| 0000 | 0 | 0 | |
| 12 | 12 | 12 | 12 |
| null | null | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
"000123" becomes "123" and "0045" becomes "45". The trap is "0000": trimming every zero leaves empty text, which is neither the code nor null. The Safe column turns that empty result into "0". Decide what an all-zero code should mean in your data, and write it down as a rule.
The last column shows the other route, Number.FromText, which gives the numbers 123, 45, 0 and 12, and null for the null row. Use it only when the result really should be a number. If any code contains a letter, such as "00A12", Number.FromText returns an error for that row, while Text.TrimStart simply returns "A12".
Trimming works the same way on the right with Text.TrimEnd, and Text.Trim removes the character from both ends. That is rarely what you want for codes, because a code can legitimately end in zero.
Merges that fail because of leading zeros
The most expensive leading-zero problem is a merge that returns no matches. One table holds order numbers as text with zeros, and another holds them as numbers:
let
Orders = #table({"Order No", "Total"}, {{"000123", 40}, {"004560", 15}}),
Shipments = #table({"Order No", "Carrier"}, {{123, "Royal Mail"}, {4560, "DPD"}}),
Merged = Table.NestedJoin(Orders, {"Order No"}, Shipments, {"Order No"}, "S", JoinKind.LeftOuter),
Out = Table.ExpandTableColumn(Merged, "S", {"Carrier"})
in
OutResult
| Order No | Total | Carrier |
|---|---|---|
| 000123 | 40 | null |
| 004560 | 15 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Every Carrier is null. There's no error, because a text value and a number are simply never equal. With an inner join the orders would vanish instead, one of the causes in finding where rows go missing. Convert one side to match the other before merging. Here the shipments are padded to the same six-character text:
let
Orders = #table({"Order No", "Total"}, {{"000123", 40}, {"004560", 15}}),
Shipments = #table({"Order No", "Carrier"}, {{123, "Royal Mail"}, {4560, "DPD"}}),
Fixed = Table.TransformColumns(Shipments, {{"Order No", each Text.PadStart(Text.From(_), 6, "0"), type text}}),
Merged = Table.NestedJoin(Orders, {"Order No"}, Fixed, {"Order No"}, "S", JoinKind.LeftOuter),
Out = Table.ExpandTableColumn(Merged, "S", {"Carrier"})
in
OutResult
| Order No | Total | Carrier |
|---|---|---|
| 000123 | 40 | Royal Mail |
| 004560 | 15 | DPD |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now order 000123 finds Royal Mail and 004560 finds DPD. Which direction you convert depends on which form you treat as the standard. Pick one and use it for every query that touches that key. The lesson on checking key quality before merging shows how to catch mismatched key types before they cost you a report, and the exercise clean both keys before merging practises it.
Leading-zero checklist
- Open the Changed Type step after every new import and check each identifier column is Text.
- If zeros are already lost and the width is fixed, pad with
Text.PadStart; if the width isn't fixed, find the rule instead. - When removing zeros, handle the all-zero case and keep the result as text unless you need arithmetic.
- Before a merge on a code, make sure both columns have the same type and the same form.
Practise keeping leading zeros
The exercise keep order numbers' leading zeros starts from an import that loses them, and make a mixed number/text code column all text handles a column where some codes are numbers and some are text. Both run in the browser on Power Query Exercises, where each answer is checked against the expected table.
Frequently asked questions
Will Excel keep the zeros when the query loads?
It depends on the column type the query outputs. If the query outputs a Text column, Excel receives the text "000123" and shows it as written. If the query outputs a number, the zeros were lost in Power Query and need padding there.
Can I keep the column as a number and just display the zeros?
In Power Query, no: the editor shows numbers without leading zeros. A custom number format in Excel can display them, but the stored value is still a number, so a Power Query merge, a lookup or a formula that reads the cell sees 123, not 000123.
Is Text.PadStart the same as Excel's TEXT function?
For codes, Text.PadStart(Text.From([Code]), 6, "0") does the job of =TEXT(A2,"000000"). The difference is the input: Excel's TEXT takes a number (or text that looks like one), while Text.PadStart needs text, hence Text.From. The guide to Excel formulas and their Power Query equivalents lists other common translations.
Sources
- data types article (Microsoft Learn) (learn.microsoft.com)
- Text.PadStart, Microsoft Learn (learn.microsoft.com)
- standard numeric format strings page (Microsoft Learn) (learn.microsoft.com)
- Text.TrimStart, Microsoft Learn (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Fuzzy matching in Power Query: standardise inconsistent namesStandardise inconsistent product names in Power Query: normalise text first, let fuzzy merge suggest matches, then keep checked matches in a mapping table.
- Cleaning phone numbers and postcodes with Power QueryStandardise UK phone numbers and postcodes in Power Query: keep only digits, fix +44 and lost leading zeros, re-space postcodes and flag values that fail.
- How to split full names into first and last names in Power QuerySplit a full name column into first and last names in Power Query, with rules for middle names, single names, 'Last, First' order, titles and suffixes.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.