Forum Discussion
How to change column format?
Hello,
I want to change in a column the text from 1,2,3,4,5...,11,12,13,...111,112,113 to 001,002,003,...,011,...111,112,113.
I tried format(text,"000") but it does not work.
My problem is that I join 3 columns to a key e.g. VS00001-1-1 and I want that it looks like VS00001-01-001 and so on.
Someone an idea.
Thank you for your help and time!
You will need to add a custom column, and then add the following formula.
=Text.PadStart(Number.ToText([Column1]),4,"0")
The red section converts the numeric column to text. This is required before you can pad out from the left
The blue section then pads out the text string to be (in this case 4 characters long using a "0" as the padding character).
6 Replies
Hello I would like to change a column which has values 1,2,3,4,5,..., 150,151,152 in 001,002,003,004,005 and so on.
I tried it like in Excel with format(text,"000") but it does not work.
Anyone an idea?
Best wishes,
Seb
Duplicate post
You can use Custom Column in Query Editor,
As shown in the screenshot, Write Custom Column and once it is done delete your original column if not required.
Alternatively, You can use CONCATENATE function as well.
You will need to add a custom column, and then add the following formula.
=Text.PadStart(Number.ToText([Column1]),4,"0")
The red section converts the numeric column to text. This is required before you can pad out from the left
The blue section then pads out the text string to be (in this case 4 characters long using a "0" as the padding character).
perfekt thank you Matt!
also a big thank you for the explanation!
Hello Bhavesh,
this did not work out because it is the same like to add a präfix.
1 turns to 001
41 to 0041
I need
1 to 001
41 to 041
But thank you for the idea!
The CONCATENATE function also doesn't help. I tried before and it does not work like in excel.
