Power Query – Adding Leading Zero to Number (EG: Month Number)
Below is an example if you have a requirement and you want to add a leading zero
Example: You have a Month Number that starts with “1”, and you want it to start with “01”
-
In your Power Query Window, click on Add Custom Column and then put in the following syntax
Text.PadStart(Text.From([Month]),2,”0″)
- NOTE: In the above example, we have our column name as Month
-
So once completed it will look like the following:
Thank you! I have tried to find the equivalent for TEXT / FORMAT for a long time. You have saved me a lot of steps!
Glad it was helpful
Works perfectly, thanks!
Awesome, glad you found it useful
doesnt work for me.
The syntax for ‘.’ is incorrect. (DAX(Text.PadStart(Text.From([Week no.]),2,’0′))).
Hi there, just remove the “.” at the end
That should get it working?
Thanks! This was a simple fix that I couldn’t manage to find on my own. The internet is so powerful.
It is a pleasure, glad to help you out!
How can I combine 2 pieces of code with each other? I don’t want to make a new column.
= Table.TransformColumns(#”Extracted Year”,{{“Month”, Date.Month, Int64.Type}})
with
Text.PadStart(Text.From([Month]),2,”0″)
This should work below:
= Table.TransformColumns(#"Extracted Year",{{"Month", Text.PadStart(Text.From(Date.Month),2,"0"), Int64.Type}})Just ensure that the double quotes are correct
Thanks!
Pleasure, glad it helped
Very helpful. Thanks a lot. It worked for me!
Awesome, thanks for letting me know.
This is the error message I’m getting: We cannot apply field access to they type Function Details: Value=Function Key=CUSTOMER_NO
Any thoughts?
Hi there
You might have to convert your CUSTOMER_NO to a text data type by putting this code into your M Query
Text.From([CUSTOMER_NO])Thanks for help
Thanks it helped
Awesome, thanks for letting me know.
This was perfect! Exactly what I needed it to do. Thank you
Awesome, thanks for letting me know!!!
I am getting an error.
Text.PadStart(Text.From([facility_id]),6,’0′)
Token literal expected
Hi there,
If you could please try the text below where it has got the double quotes on the zero
Text.PadStart(Text.From([facility_id]),2,"0")How can I add 2 leading zeroes in front of a number? It’s a similar situation, I have a list of numbers, at most with 6 digits but there are some that have 5 or 4 digits. I want all numbers to be 6 digits, so would want 1 zero in front of the 5 digit numbers and 2 zeroes in front of the 4 digit numbers. Is this doable?
Hi there,
You should be able to do it by using the code below
Text.PadStart(Text.From([Month]),6,"0")