Forum Discussion

gusdahu's avatar
gusdahu
Icon for Helper IV rankHelper IV
7 years ago
Solved

How do I add Leading Zeros?

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

Need Help
  • Henri-M's avatar
    Henri-M
    7 years ago

    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

3 Replies

  • Henri-M's avatar
    Henri-M
    Regular Visitor
    7 years ago

    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

    • gusdahu's avatar
      gusdahu
      Icon for Helper IV rankHelper IV
      7 years ago

      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