M code newb - question about custom formula to add a leading zero

[Guitarmageddon]

So in my source data, I have a column that is all store numbers. We use 5 digit site numbers, and some have leading zeroes until the numbers get high enough.

For example, store number:

01100
02100
05100
Then on to 14100, 15100, 16100, and so forth....

Power query gets rid of my leading zero. When I right click on that column within power query, and try to change type to text, it still gets rid of the leading zero. So Im not sure if there is an easier way to do this. I know in mcode there is a way to use IF statements but Im very new to it.

Basically Im trying to do something along these lines, but just say to excel, "if the Site Code column is less than 5 digits (since excel cuts the leading zero) then add a leading zero at the beginning. If its not, dont do anything.

= Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({"0", [Site Code]}), type text)

This is in the ballpark, but Im not sure how to tell it to stop when I get to the store numbers that require no action. Thoughts?

Like if Text.Length function returns less than 5, then Text.Combine etc

 

[Guitarmageddon]

Disregard this....so I ended up simply doing it again, and it gave me the option to transform to text, replace current column, and it in fact kept the leading zeros..... odd.

HOWEVER....if this is easy for someone to do, could you take me through the steps above if I hadnt gotten it to work?

 

[RoryA]

You could combine "00000" with your number, then use Text.End(text, 5) to just get the last 5 digits so you only get leading zeroes if needed. Or use Text.PadStart(text, 5, "0")

 

[citizenbh]

After import in PQ change Int64.Type with Text.Type for subject column in step "Changed Type"