columns with mixed data types

[mattymc1984]

Hi,

I'm working with debtor/invoice data.  One of my fields is "Account Number".  Roughly 80% of these are numeric, however internal customer numbers have a letter prefixing the account number.  Power Query auto detects this column as numeric and when I load the data into Excel or PowerBI it returns all the account numbers with letters in them as errors, which I understand.  However I need to have all account numbers shown, so I tried to select data type as text, but this doesn't work either and the error report says that Power Query is still trying to convert these alpha strings into numeric.

Any help would be much appreciated!

Matt

[MarcelBeug · Accepted solution]

If you add an extra step to change number back to text, the errors won't be corrected, so you should adjust the autogenerated step in which the data types were changed and adjust "number" to "text" for the Account Number field.

[mattymc1984]

This did it thanks!

As you said, I went to the auto-generated steps where Power Query first decides on the data type of each field and changed the "Account" type to text.  Sorted.  Thankyou.

[vanessafvg]

when you say you tried to change it to text its still keep it as numeric it what do you mean, it should correct the problem if you change it to text, can i see your power query steps?  

[mattymc1984]

This is the line of code for changing the data type:

 #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Account", type text}}),

However, when I load the data into Excel, it still returns all the alpha account numbers as errors:

DataFormat.Error: We couldn't convert to Number.
Details:
    A003

[MarcelBeug]

Refereing to my previous post: that doesn't look like the line of code with the initial type change (because the "1" in the step name and because it is referencing previous step #"Filtered Rows") .

My suggestion would be to delete this step and adjust the #"Changed Type" step (probably the second or third step in your code).