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
How can I get this to work : = Table.TransformColumns(#”Replaced Value5″,{{“plant”, each Text.PadStart(Text.From([plant]),4,”0″), type text}})
Hi Jocelyn,
What I would suggest doing is to do it in individual steps to make sure you can get it working. Once that is done then look to combine the steps into a single step if required.
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")Thankssssss
Thanks for the comment Samira!
Thank you very much! it was really great to find your post.
I appreciate the comment!
Thanks it helped
Thanks for letting me know!
Thanks it helped
Thanks for letting me know.
Thanks for letting me know.
Thanks
Thanks
Thanks!!!!
Pleasure, glad to help!
Thanks for this. I expanded it to get a Year-Month Key Column that I needed.
= Table.AddColumn(#”Previous PQ Step”, “Year Month Key”, each Text.Combine({Text.From([Year], “en-GB”), “-“, Text.PadStart(Text.From([Month], “en-GB”),2,”0″)}), type text)
Thanks Clem good to know that there are additional ways to get this to work!