How to change column format?

[SebFelSch]

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!

[BhaveshPatel]

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.

[SebFelSch]

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.

[MattAllington · Accepted solution]

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).

[SebFelSch]

perfekt thank you Matt!

also a big thank you for the explanation!

[SebFelSch]

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

[BhaveshPatel]

Duplicate post