Forum Discussion
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 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.
11 Replies
I am getting an Expression.Error: The name 'FORMAT' wasn't recognixed. Make sure it's spelled correctly.
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. =(
Thank you so much Ross
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-detection-of-data-types/
Thank you nchambe! Sorry I forgot to update...it turned out that the issue was in SQL Server, not Power BI.
