Forum Discussion

Soc3's avatar
Soc3
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 Pow...
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    What's the datatype in Power Query?

    and

    have you tried padding with Text.PadStart?

ronrsnfld's avatar
ronrsnfld
Icon for Super User rankSuper User
4 years ago

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