All characters after 15 characters getting converted into zeros after changing data type to text

[bhushan_7]

Hi,

I have numbers in excel (data source). Numbers are of various lengths. In all numbers where characters are more than 15, automatically zeroes appear, replacing any characters present in the original number.

I need this in text as I want to append this with another text. Adding an @ mention as I saw a solution in another post but that is to a different issue.

@AllisonKennedy 

[ronrsnfld]

The values in Excel are text strings.

PQ will automatically detect those text strings as numbers, and reduce them to a maximum of 15 digits precision.

If you want to maintain them as the original values that you see in Excel, you need to prevent that conversion from happening in PQ.

In PQ, open the Advanced Editor (on the Home tab)

you will see a line near the top of the code that starts with:

  #"Changed Type" = Table.TransformColumnTypes(Source, {{"ColumnName", type number},{"Another ColumnName", Int64.Type},.....

For the entry where "ColumnName" is the name of the column where your data exists, merely change type number or Int64.Type to type text.  And, unlike Excel, the case (capitalization) IS important.

[serpiva64]

Hi,

this is what you get by adding a custom column

= Table.AddColumn(Source, "Custom.1", each if Text.Length([Column1]) <= 15 then [Column1] else Text.Start([Column1],15)& Text.Repeat("0",Text.Length([Column1])-15))

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!