Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
What's the datatype in Power Query?
and
have you tried padding with Text.PadStart?
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
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
What's the datatype in Power Query?
and
have you tried padding with Text.PadStart?