Forum Discussion
Leading Zero Issue
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.
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.
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?
The best way to get help is to share your existing dataset (inout) and show the expected result (Output).
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.