How to Stop Power BI from Removing Leading Zeros

[ninsights]

I am using a table from our SQL Server which has a TEXT field called StoreNo.

These store numbers have leading zeros. For example "00489" and  "01157."

When I load the data source into Power BI (import, not direct query), it ignores the fact that StoreNo is a text field and makes teh field a number.

I realize that I can create a column to pad the number with zeros, but since the field in the original table is a text field, I'd like to know how to STOP power BI from assuming this field is a number field and import the values of the field as they are, i.e., have them import as "00489" and "01157."

I need this for many applications, so a solution is greatly appreciated.

[Icey]

Hi @ninsights ,

I don't know how to stop Power BI from transforming your "StoreNo." column from Text to Number in your scenario.

But there is one workaround, please check:

1. Create a custom column in Power Query Editor.

= Number.ToText([#"StoreNo."],"00000")

2. Remove your original "StoreNo." column.

3. Rename "Custom" column with "StoreNo.".

Best Regards,

Icey

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

[ZCE31]

Hi Icey, thank you for the tutorial, it was helpful for most of my columns. however, when increasing the number of zeroes in your formula to 9, to add the leading zero back to a 8 digit number, I just got an error message. Do you have any other suggestions for this - or would I have to load the data in again completely, making sure that column is designated as text only? Many thanks.

[Jwilcox555]

This solution worked perfectly, thanks!

[ninsights · Accepted solution]

Thank you @Icey and @parry2k . I spoke with the creator of the SQL table and while it displayed the leading zeroes in the table, the field has been defined as FLOAT. Since it was a new table, the creator was willing to re-import the data into the SQL table with the field defined as varchar.

I will keep the workarounds for the future since redefining a table is not always going to be an option.

Thank you both!

[Icey]

Hi @ninsights ,

Glad to hear that you have solved the problem. Please accept your reply above as the solution. Your contribution is highly appreciated.

Best Regards,

Icey

[parry2k]

@ninsights in the change type step, make sure it it not converting it to number, and change it to text. Make sure not to add another step to convert to text but change existing step where it change column type. Would appreciate Kudos 🙂 if my solution helped.

[ninsights]

@parry2k 

I don't actually have any type change steps. Power BI is automatically converting this field to a number. I would like to stop the conversion it's doing when loading my table.

The "advanced editor" displays the following as my entire query. You will see that the only action I've taken is to pick a table. I've typed generic table/database names here.

let

   Source = Sql.database("name-sql","ABC"),

   dbo_852VendorNameStores = Source{[Schema="dbo",Item="852VendorNameStores"]}[Data]

in

   dbo_852VendorNameStores

[parry2k]

@ninsights this is interesting, I guess data type in your table is varchar or something like that, correct?