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

[Soc3]

I have a column (employee ID) that either begins with A or zero. All employee IDs should be 8 characters long. The records that begin with A have no problem, but when importing my Excel file into Power BI, all of the records that begin with zero lose the zero. In Excel, I would format the cell to be custom and make it 0#######. How do I do this in Power BI? Adding a prefix of zero adds it in front of the A records, which would make it 9 characters long and incorrect. I can't change the data type to numeric, as not all records are numeric.

How do I reinsert the leading zeros for only the numeric records?

[ronrsnfld]

If you don't want to change your source to make these entries text, then you can do a transform on the Employee ID Column.

Assuming your alpha ID's are the correct length and do not need to be padded, you can use this code:

let

//generated code to get the table from the Excel workbook
    Source = Excel.Workbook(File.Contents("C:\Users\ron\OneDrive\Documents\Book1.xlsm"), null, true),
    Employees_Table = Source{[Item="Employees",Kind="Table"]}[Data],

//change to data type text
    #"Changed Type" = Table.TransformColumnTypes(Employees_Table,{{"Employee ID", type text}}),

//pad with leading zero's to 8 characters
    pad = Table.TransformColumns(#"Changed Type", {"Employee ID", each Text.PadStart(_,8,"0")})

in
    pad

Source

Results

[mahenkj2]

@HotChilliText.PadStart is way cleaner solution. Learning everyday...

[mahenkj2]

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

[HotChilli · Accepted solution]

What's the datatype in Power Query?

and

have you tried padding with Text.PadStart?