Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
decarsul
Helper IV
Helper IV

Issue: queryM automatically adds leading zeros that should not exist

Good day,

 

Today i been importing excel files and come across an issue i haven't had before.

For some reason, during the table / column transform step my key columns are transformed from text to integer.

Now this in itsself isn't a problem, however when removing that transform, i get 2 leading zero's added that never existed to begin with.

 

These leading zero's also do not accumulate to an fixed length for the key columns, and as such make absolutely no sence.

Now i can trim them back to their original format. However i would like to prevent it happening in first place.

Google and searching this forum only shows how to add or remove leading zeros, but none how to prevent them.

 

If anyone has an idea how to prevent them from being magically summoned, please let me know!

 

decarsul_0-1712127623267.png

 

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

Are you certain the leading zero's are being added by your query?

Are those leading zero's present at the initial step in Power Query before any transforms?

Are the leading zero's present in the Source file (excel)?

 

Please post an example of what you see in the actual Source file (Excel), what you are seeing the first Applied Step in PQ, and what you want as an outcome.

View solution in original post

7 REPLIES 7
ronrsnfld
Super User
Super User

Are you certain the leading zero's are being added by your query?

Are those leading zero's present at the initial step in Power Query before any transforms?

Are the leading zero's present in the Source file (excel)?

 

Please post an example of what you see in the actual Source file (Excel), what you are seeing the first Applied Step in PQ, and what you want as an outcome.

Ok, thank you for these questions as they made me 2nd geuss myself.

And you were on to something.

 

The sourcefile DOES have the leading zero's, but they were not visible to me since Excel does its automagic. Opening the source file with a proper editor shows the leading zero's being in the source. And to make it even better. The data is not clean, one document has leading zero's where the other one has not. . . 

 

Check your basics. Thanks for asking the questions!

Yes, when Excel is dealing with text/csv files, it is always important to go back to the original source, and also to import (using Power Query or the legacy import wizard) rather than open, unless very certain of the data and how Excel will transform it when executing the Open.

ToddChitt
Super User
Super User

Excel recently added the feature to retain leading zeros in cells that have them, like Zip Codes. If your data came through Excel, I would verify the source data BEFORE it went through Excel and see if it did anything unwanted to the data. In my experience, I try to keep data out of Excel when bringing it into Power BI. I have seen Excel do stuff that totally messes up data. 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





dufoq3
Super User
Super User

Hi, it is hard to guess where they come from, but you can covert this column to number and back to text to remove them. (Add this as new step). This will remove leading spaces and your column will be stored still as text.

 

= Table.TransformColumns(#"Added Custom", {{"Transaction ID", each Text.From(Number.From(_)), type text}})

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

I appreciate the suggestion, but this is basically the same as trimming the leading zeros. 

Yes, it is.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors