power query m converts text to numeric but loses the 0 at the beginning of the number

[Riddim]

Hi,

power query m converts text to numeric but loses the 0 at the beginning of the number.

For example:

0012 becomes 12

0123 becomes 123

try Number.FromText(text as text) as number

or

try Text.From( Number.FromText(text_column_number) in a new column doesn't work.

i need to keep the 0.

I haven't found a topic for this case.

Can you help me?

[Vijay_A_Verma · Accepted solution]

If you need to keep leading 0s, then you will need to retain the number as text only. Number format will not be able to show leading 0s. In Excel, you can format numbers with leading 0s but here there is no column formatting available on number columns to retain leading 0s. 

[v-junyant-msft · Accepted solution]

Hi @Riddim ,

@Vijay_A_Verma Thank you for your replying! This indeed can't be achieved in Power Query, but it can be achieved in table view:
In Model view, choose the data column:

Then set it as below:

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Riddim]

Thanks a lot

[Vijay_A_Verma · Accepted solution]

If you need to keep leading 0s, then you will need to retain the number as text only. Number format will not be able to show leading 0s. In Excel, you can format numbers with leading 0s but here there is no column formatting available on number columns to retain leading 0s. 

[bhanu_gautam]

@Riddim , Try using below method

try Text.PadStart(Number.FromText([TextColumn]), Text.Length([TextColumn]), "0")

[Riddim]

It didn't work. it failed to convert text to a number(it offers hasError and noHasError)