Substitute for Excel Function: TEXT(A2,"000000")

[porter444]

I have a workbook that contains bin locations. the location format is 6 digits, but sometimes on export if the leading digit of the location is a zero it gets left out (type becomes number). In the past I dealt with this by adding a column to convert the location to a 6 digit format with TEXT(A2,”000000″).



I’m now trying to leave those old Excel tricks behind. I’m importing the workbook via powerquery and want to handle the formatting issue there. I change the type to TEXT, but then am not sure how approximate the Excel TEXT( function in powerquery.

Sample data attached.

 

[Ken Puls]

Here's how I approached it:

• Pull the data into Power Query
• Change the data type of the first column to Text. (Your preview will default to numeric, but around line 478 you have text combinations... you'll get errors later if you skip this step)
• Rename the first column to Location_old
• Go to Add Column --> Add Custom Column

And here's the formula for the new column (which I called Location)

Text.End("000000"&[Location_old],6)

Sample attached.

 

[porter444]

Thank you sir!

 

[omezquita]

An alternative approach would be to follow the same steps mentioned by Ken, but use the Text.PadStart function instead.

The function would be:

Text.PadStart([Location], 6, "0")

Regards,
Orlando