Column format not pulling through correctly from Excel source

[Jumper]

Hi.  I've got a data column in an Excel file that is formatted to 000, as it is a detail that needs to be viewed as a three digit code.

But when I pull the detail into Power BI, any leading zeros seem to be getting dropped off.  

These three digit codes are alpha-numeric, so the column has imported as a general format.  Unfortuantely without the leading zero, the codes are not ordering as expected, nor are they sticking to the three digit format that is necessary.

Any suggestions how to fix?

[v-jianpeng-msft]

Hi, @Jumper 

When you import Excel data into Power BI, Power BI will automatically identify the data type of each column and set the corresponding type based on your data in each column. You can undo this step in Power Query, and then you can set the data type of each column according to your actual situation.

I have created the following sample data in Excel:

The results of importing to Power BI are as follows:

The steps to restore leading zeros are as follows:

With the above steps you can restore the leading 0's in your columns.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jolind1996]

In Power BI, you can retain leading zeros by converting the column to text format. Use the FORMAT function or concatenate an empty string to the alpha-numeric codes. For instance, use a formula like =FORMAT([Column],"000") or =[Column]&"". This ensures that the three-digit format is maintained and leading zeros aren’t dropped when importing data from Excel. This should help your codes order as expected and maintain the necessary three-digit format. Remember, Power BI treats numbers without leading zeros as numerical data, hence the need for this conversion. I hope this helps! If you have any other questions, feel free to ask.

Best regards,
Johannes