Padding Zeros for Numeric Values Only in a Column with Mixed Data Types

I would consider two options:

1. At source, instead of formatting the cell, add ' before all values of the column of interest. This shall ensure that you import all values as text and initial 0 is not trimmed.

2. Assuming that you would not like to change at source, just couple of transformation in power query can give you desired output:

a. Check if the imported column is Numeric by adding a column:

Value.Is([ID], Int64.Type)

b. Then use this column in another column:

if [#"Numeric?"] then "0" & (Number.ToText([ID],"D")) else [ID]

This will check if column added in step a above is numeric and if so, will concatenate a 0 in that value else will keep original value.

This column is your final column.

c. Now you can remove your original imported column and the column added in step a above.

Done!

A sample file is here:

https://drive.google.com/file/d/1kMRJ9ZEiro1FfRTV9GZU4OEBnilzQu7c/view?usp=sharing