How do I add Leading Zeros?

[gusdahu]

Power BI Community,

I have a data set that includes vendor IDs. Each vendor ID is ten digits long and usually begins with six or seven zeros followed by another digit. See examples below:

Vendor ID: 0000001234 or 0000000567

Power BI is currently pulling this information  from an excel worksheet. The data within my worksheet is formatted as text. However, when I upload the data into Power BI, the leading zeros are removed and I am left with the numerical values. See examples below:

Vendor ID: 1234 or 567

I attempted to trouble the following two ways:

1) Changed the format of vendor ID to text using the edit query functions. However the data was already formatted as text; and

2) Attempted to created a new column using the dax expression "Vendor ID v2 = FORMAT ([Vendor ID],"0000000000").

Neither method worked for me. Any suggestions on how I may add the leading zeroes will be appreciated.

Thank you,

Gus Dahu

[Henri-M · Accepted solution]

Hi,

I propose two approaches:
Approach 1: When your data is loaded in power query look on the right side your query settings and DELETE CHANGED TYPE

Approach 2: Go to Your Power Query

File =>Cuurent File =>Data Load => Type Detection: UNCHECK "Automatically detect colum types and headers for..." and reload your data.

Tell me if it's OK.

Thanks

[ChrisMendoza]

@gusdahu -

In the Query Editor you use Text.PadStart.

Your #2 works for me to pad with leading.

[gusdahu]

ChrisMendoza,

Unfortunately Text.PadStart did not work for me. I ended up changing the text format in the source document and it everything worked out for me. Thank you for your help.

Regards,

Gus Dahu