Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Soc3
Frequent Visitor

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?

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

What's the datatype in Power Query?

and

have you tried padding with Text.PadStart?

View solution in original post

4 REPLIES 4
ronrsnfld
Super User
Super User

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

ronrsnfld_0-1651923372399.png

 

Results

ronrsnfld_1-1651923406227.png

 

 

 

mahenkj2
Impactful Individual
Impactful Individual

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

mahenkj2
Impactful Individual
Impactful Individual

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!

 

Capture.JPG

A sample file is here:

https://drive.google.com/file/d/1kMRJ9ZEiro1FfRTV9GZU4OEBnilzQu7c/view?usp=sharing

 

HotChilli
Super User
Super User

What's the datatype in Power Query?

and

have you tried padding with Text.PadStart?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors