Issue: queryM automatically adds leading zeros that should not exist

[decarsul]

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!

[ronrsnfld · Accepted solution]

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.

[decarsul]

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!

[ronrsnfld]

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]

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. 

[dufoq3]

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}})

[decarsul]

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

[dufoq3]

Yes, it is.