Forum Discussion

Soc3's avatar
Soc3
Helper 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 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?

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    What's the datatype in Power Query?

    and

    have you tried padding with Text.PadStart?

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    What's the datatype in Power Query?

    and

    have you tried padding with Text.PadStart?

  • ronrsnfld's avatar
    ronrsnfld
    Super 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

     

     

     

  • mahenkj2's avatar
    mahenkj2
    Solution Sage
    4 years ago

    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