Forum Discussion

Krish_76's avatar
Krish_76
Frequent Visitor
9 years ago
Solved

Leading Zero Issue

Hello everyone   I am having trouble with the Zip codes when it has 0 as the leading.   Example : I have data like this in excel  NJ   07306 TN  38120   When i imported into Power BI, it is ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    Quick solution is to create a new custom column in DAX with a formula:

    FullZip = FORMAT([Zip], "00000")

    Where [Zip] is your zip code field name.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Quick solution is to create a new custom column in DAX with a formula:

FullZip = FORMAT([Zip], "00000")

Where [Zip] is your zip code field name.

d-mo's avatar
d-mo
Regular Visitor
8 years ago

I am getting an Expression.Error: The name 'FORMAT' wasn't recognixed. Make sure it's spelled correctly.

  • ACT's avatar
    ACT
    Frequent Visitor
    8 years ago

    I am too...I tried to write it within the Advanced Query Editor rather than creating a new column as well...tried to edit format changes, etc. Can't seem to get it to work. =(

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      The solution offered by Ross is a DAX one (not an M one).  If you wish to solve this problem in Power Query, please read here.

       

      Hope this helps.

      • ACT's avatar
        ACT
        Frequent Visitor
        8 years ago

        Ugh, thanks Ashish and Ross...however, it's not fixing my issue! I'm working with ID's, and some of them have 3-4 leading zeros...trailing zeros are not being deleted.

         

        The original column is formatted as text...the calculated column seems to be referencing the correct table/column (as opposed to drawing from the incorrectly trimmed column)...what am I missing here? What else can I do?

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      This is because its a DAX created column, not a Power Query formula.

       

      EDIT:  You beat me to it :P