Add zero to any 7 digit number starting with 2 in power query

[Tcowley44]

Hi all,

Trying to come up with a formula that will add a zero to any 7 digit number in a colum starting with a 2, nothing else. So far I get an error or add a zero to all 7 digit numbers. 

Thanks

[Vijay_A_Verma]

I am not sure what is meant by adding 0 to this number. 

If you wanted that a 0 should come at the end, then use below formula

= if Number.IntegerDivide([Data],1000000)=2 then [Data]*10 else [Data]

If you wanted 0 as prefix, then the output will be in text format and you can use following formula in this case

= if Number.IntegerDivide([Data],1000000)=2 then "0"&Text.From([Data]) else [Data]

[Tcowley44]

Hi Viay,

Thanks for the reply. A little more info. I need to add the 0 to the beggining of the number in my ship ref1 colum. the 7 digit numbers starting with 2 represent our PO#'s. They are supposed to have a zero and it isnt always included.  I still get an error when I tried you example. 

[Vijay_A_Verma · Accepted solution]

Use this

= if Text.Length([Data])=7 and Text.Length(Text.Select([Data],{"0".."9"}))=7 and Text.Start([Data],1)="2" then "0"&[Data] else [Data]

[Tcowley44]

Worked Perfectly! Thanks