Leading Zero Issue

[Krish_76]

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 importing in this format

NJ    7306

TN    38120

I went in to query editor and tried to change the format as TEXT but it didn't help me. If there was only handful then i would have replaced it but there are so many of them and i am not sure how to replace them.

Can anyone please help me on this. Because right now the map is showing all over the world (Instead of just US) when i place the Zip codes.

[nchambe]

Changing the data format to text should resolve this issue as long as the original data has the leading zeroes. I've demonstrated it here: https://sharepointlibrarian.com/2018/09/06/leading-zeroes-in-power-bi-restoring-them-from-auto-detec...

[ACT]

Thank you nchambe! Sorry I forgot to update...it turned out that the issue was in SQL Server, not Power BI.

[Anonymous · Accepted solution]

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]

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

[ACT]

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. =(

[Anonymous]

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

EDIT:  You beat me to it 😛

[Ashish_Mathur]

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]

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?

[Ashish_Mathur]

The best way to get help is to share your existing dataset (inout) and show the expected result (Output).

[ACT]

The original data is coming from a SQL Server DB as varchar(20) values, all of them 7 numerical digits. There should be no variation.

All I want is for PowerBI to import it and maintain that format. i.e. Stable 7 digit IDs, regardless of leading or trailing zeros.

EDIT: I work with protected health information, so I have to be careful about what I post. I definitely cannot share my dataset, but I'm trying to troubleshoot...from the earliest parts of the query the IDs are coming up truncated.

Hopefully that's enough info, but let me know what else you need if not. I'm trying to re-write the query by hand to import the data differently...it looks like it's pulling the whole table as [Data], I'm hoping I can specify a different format for selected columns and that the issue is that Power BI is guessing the format of IDs wrong.

[Krish_76]

Thank you so much Ross