Converting Text Column having Text and Scientific Notation

It all depends upon what you want to downstream. Formatting is something which is not important in mashing up data unless needed. 

Hence, if in the downstream, you want to extract numbers, you can have following formula

= try Number.From([Column1]) otherwise Number.From(Text.Select([Column1],{"0".."9"}))

But it all depends what you want to do.